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 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
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
Show quoted text
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:
Show quoted text
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
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
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 |
+------------------------------+---------------------------------------------+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