Re: weird quote bug - Mailing list pgsql-general

On 30/06/2003 12:25 Shane Wright wrote:
> Hi
>
> This is really driving me silly - I can't work it out, can anyone see
> what I'm doing thats stupid and causing this not to match?
>
>
> This shows that the row exists in the table:
>
>
> emystery=> select aid,useragent from useragent where useragent like
> '%ntserver-ps%';
>     aid    |                                                useragent
> -----------+---------------------------------------------------------------------------------------------------------
>  875741007 | Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
>
> this tries to select it (note that I've escaped the backslashes):
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
>  aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+---------
> (0 rows)
>
>
> no match!  This one encodes the backslashes (\xxx octal for ASCII value)
> in a different way:
>
>
> emystery=> select * from useragent where useragent ilike 'Mozilla/4.0
> (compatible; MSIE 6.0; Windows NT 5.0;
> file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
>  aid | useragent | date_added | data_browser | data_version | data_os
> -----+-----------+------------+--------------+--------------+---------
> (0 rows)
>
>
> again no match!  And to show that the above queries were correct:
>
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\\\ntserver-ps\\publicsw\\nt\\ie6\\ins\\active.ins)';
>                                                 ?column?
> ---------------------------------------------------------------------------------------------------------
>  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
> emystery=> select 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\134\134ntserver-ps\134publicsw\134nt\134ie6\134ins\134active.ins)';
>                                                 ?column?
> ---------------------------------------------------------------------------------------------------------
>  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0;
> file://\\ntserver-ps\publicsw\nt\ie6\ins\active.ins)
> (1 row)
>
>
>
> They are identical!  I can't work out whats going on!  Please, if anyone
> can see what's wrong it'll stop me careering into my early grave!

I've never tried this but the docs for LIKE (secfion 6.6.1 in the 7.3
docs) say that to match a literal \ you need to type \\\\. An alternative
might be to try adding an ESCAPE '' clause at the end (also in the same
doc section).

HTH

  --
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+