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)