Обсуждение: weird quote bug
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
tomatch?
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
intomy early grave!
Thanks
Shane
Should have said - I'm using postgreSQL 7.3.3 on Gentoo Linux. The problem occurs both through psql and through PHP4.3.3. Cheers Shane On Monday 30 Jun 2003 12:25 pm, 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! > > Thanks > > Shane > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
you might want to try escaping the simple slashes aswell... and you have to double the number of backslashes.... learning=> SELECT * FROM test where x like '\\\\'; x --- \ (1 row) *tadaaa* cheers, alex 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 notto 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 intomy early grave! > > Thanks > > Shane > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
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 | +------------------------------+---------------------------------------------+
> 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)
Are you sure there's no trailing whitespace on this string ?
Because this:
> 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)';
^
would not match trailing whitespace.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
> > 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). Ah. I had soooo read that bit of the manual - must have missed it. Phew it works now! Thanks (and thanks to the others who answered my question!) Cheers Shane