Re: query to match '\N'

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)