Thread: 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 quoting
>
>
> 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.
>

or..
    a LIKE 'a\\\\\'%'

thanks, I get it now :)

--- Tim