On Fri, 27 Jul 2007, pc wrote:
> Hi,
>
> I have a table test with columns col1 col2.col2 contains an entry
> '\N' .I want to select all entries which have '\N' in col2.How do i
> do that?
>
> select * from test where col2 like '\N' ;
> select * from test where col2 like '\\N' ;
select * from test where col2 like '\\N' escape ''; and
select * from test where col2 like '\\\\N';
will probably work. If you're using a recent version and turn on
standard_conforming_strings you can halve the number of backslashes, see
below.
---
On 8.2.4 with standard_conforming_strings=off (and
escape_string_warning=off)
sszabo=> select '\N';
?column?
----------
N
(1 row)
sszabo=> select '\\N';
?column?
----------
\N
(1 row)
sszabo=> select '\\N' like '\\N';
?column?
----------
f
(1 row)
sszabo=> select '\\N' like '\\\\N';
?column?
----------
t
(1 row)
sszabo=> select '\\N' like '\\N' escape '';
?column?
----------
t
(1 row)
and with standard_conforming_strings=on
sszabo=> select '\N';
?column?
----------
\N
(1 row)
sszabo=> select '\\N';
?column?
----------
\\N
(1 row)
sszabo=> select '\N' like '\N';
?column?
----------
f
(1 row)
sszabo=> select '\N' like '\\N';
?column?
----------
t
(1 row)
sszabo=> select '\N' like '\N' escape '';
?column?
----------
t
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
