Help please - completely baffled by quoting
Hi,
I generally consider myself competent with the complexities of quoting but I
can't figure out postgres at all. I've read the manual and it looks very
straightforward but that doesn't accord with my experiences. Here's an
example:
create table temp (a varchar(50));
insert into temp values 'a\'b';
insert into temp values 'a\\b';
insert into temp values 'a\\\'b';
select * from temp returns
a'b
a\b
a\'b
so far so good but what about this:
select * from temp where a like 'a\\\'%'
this should return all strings starting with a\' but it returns nothing!!!
I would expect that these two
select * from temp where a like 'a\\\'b'
select * from temp where a = 'a\\\'b'
should return the same thing. The second one works as expected, but the
first one doesn't return anything.
I'm sure I'm missing something obvious. Thanks in advance for any help.
--- Tim
Tim Robinson wrote:
select * from temp where a like 'a\\\'%'
this should return all strings starting with a\' but it returns
nothing!!!
\ is also the quoting character of LIKE, in addition to being a quoting
character on the string literal level. If you want all strings
starting with a\' then use something like:
a LIKE 'a\\\'%' ESCAPE '!'
where '!' selects some other escape character for LIKE that doesn't get
in the way. The documentation on LIKE contains the details on this
mechanism.
-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net]
Sent: 20 June 2004 14:07
To: Tim Robinson; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help please - completely baffled by quotingTim Robinson wrote:
select * from temp where a like 'a\\\'%'
this should return all strings starting with a\' but it returns
nothing!!!\ is also the quoting character of LIKE, in addition to being
a quoting
character on the string literal level. If you want all strings
starting with a\' then use something like:a LIKE 'a\\\'%' ESCAPE '!'
where '!' selects some other escape character for LIKE that
doesn't get
in the way. The documentation on LIKE contains the details on this
mechanism.
or..
a LIKE 'a\\\\\'%'
thanks, I get it now :)
--- Tim