I have the following sql command, i need to escape parentheses in PostgreSQL, how can i do that?
SELECT rua
FROM logradouros
WHERE rua ~* 'Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)'
LIMIT 100;
Add a comment
|
6 Answers
Use backslash to escape parentheses. Note that if standard_conforming_strings parameter is set to off (which is default behaviour up to PostgreSQL 9.1) you need use two backslashes \\.
Generally there are three approaches how to escape parentheses:
- ordinary
'pattern'syntax, which is dependent on standard_conforming_strings setting - escape string constant e.g.
E'pattern' - dollar-quoted string constants
$$pattern$$or$sometext$pattern$sometext$
The first one is standard SQL (especially with standard_conforming_strings), others are PostgreSQL extensions. Choose whatever method you like.
Here you have some examples:
SET standard_conforming_strings = 0;
SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~*
'Antonio De Sant\'Ana Galvao Av Frei \\(Av 01 Parte A\\)'
SET standard_conforming_strings = 1;
SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~*
'Antonio De Sant''Ana Galvao Av Frei \(Av 01 Parte A\)'
SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~*
E'Antonio De Sant\'Ana Galvao Av Frei \\(Av 01 Parte A\\)'
SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~*
$$Antonio De Sant'Ana Galvao Av Frei \(Av 01 Parte A\)$$
SET standard_conforming_strings = default;
answered Jun 3, 2011 at 15:57
Grzegorz Szpetkowski
38.1k6 gold badges94 silver badges140 bronze badges
Comments
Generally speaking, dollar quoting is your best bet. Observe:
SELECT $STR$Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)$STR$;
-----------------------------------------------------
Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)
(1 row)
Comments
Try this:
SELECT rua from logradouros where rua ~* E'Antonio De Sant\'Ana Galvao Av Frei \(Av 01 Parte A\)' limit 100;
Reference: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
Comments
Although every other response helps, I just think that using quoting functions is the 'proper' way of doing it.
from docs:
quote_literal(string text) text Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 39-1. quote_literal(E'O\'Reilly') 'O''Reilly'
Comments
just use 2 ' '
select * from games where name = 'Ahmed''s Salah'
backslash(es) didn't work for me
Comments
As for the regex, You gotta use double backslashes right in front of the parentheses!!
E.g.:
select regexp_matches('FOREIGN KEY (user_transaction_id) REFERENCES landing.user_transactions(id)', E'FOREIGN KEY \\((.*)\\) REFERENCES (.*)\\((.*)\\)', 'g');
Result:
"{user_transaction_id,landing.user_transactions,id}"
