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)

wow! works for me! Thank you !!

Hi,

Am Freitag, 27. Jul 2007, 18:35:21 -0000 schrieb pc:

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 E'\\\\N';
select * from test where col2 = E'\\N';

Why use `like' here at all?

Bertram

--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

Bertram Scharpf wrote:

Hi,

Am Freitag, 27. Jul 2007, 18:35:21 -0000 schrieb pc:

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 E'\\\\N';
select * from test where col2 = E'\\N';

Why use `like' here at all?

Presumably he wanted col2 like E'%\\\\N%'.
But doesn't \N mean NULL, or would the OP be looking for literal '\N'
strings in his data? Because if he's looking for NULLs it may be better
to query for col2 IS NULL.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Alban Hertroys skrev:

Presumably he wanted col2 like E'%\\\\N%'.
But doesn't \N mean NULL, or would the OP be looking for literal '\N'
strings in his data? Because if he's looking for NULLs it may be better
to query for col2 IS NULL.

My guess is that this string was used to signify NULL in the file
originally imported into the db.

Nis

Nis Jørgensen wrote:

Alban Hertroys skrev:

Presumably he wanted col2 like E'%\\\\N%'.
But doesn't \N mean NULL, or would the OP be looking for literal '\N'
strings in his data? Because if he's looking for NULLs it may be better
to query for col2 IS NULL.

My guess is that this string was used to signify NULL in the file
originally imported into the db.

Which is basically what I was pointing out ;)
It's all speculation anyway, we're guessing at what problem the OP tries
to solve.

I think either he is looking for NULL column values that exist in his
input file as '\N' strings (strings cannot contain NULLs, so using
"like" is pointless), or he is looking for failed conversions of \N from
his input file that thus may have ended up as literal \N characters in
column data.

In the latter case there shouldn't be any columns that match "like
'%\\\\N%'" but not "= '\\\\N'". OTOH, we may be talking about an import
failure, in which case anything is possible. Fixing that would probably
be more difficult than fixing the cause of the failure and re-doing the
import.

As I said, it's all speculation. Without input from the OP there's not
much point in continuing this discussion.

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

On Jul 30, 3:27 am, al...@magproductions.nl (Alban Hertroys) wrote:

Nis J�rgensen wrote:

Alban Hertroys skrev:

Presumably he wanted col2 like E'%\\\\N%'.
But doesn't \N mean NULL, or would the OP be looking for literal '\N'
strings in his data? Because if he's looking for NULLs it may be better
to query for col2 IS NULL.

My guess is that this string was used to signify NULL in the file
originally imported into the db.

Which is basically what I was pointing out ;)
It's all speculation anyway, we're guessing at what problem the OP tries
to solve.

I think either he is looking for NULL column values that exist in his
input file as '\N' strings (strings cannot contain NULLs, so using
"like" is pointless), or he is looking for failed conversions of \N from
his input file that thus may have ended up as literal \N characters in
column data.

In the latter case there shouldn't be any columns that match "like
'%\\\\N%'" but not "= '\\\\N'". OTOH, we may be talking about an import
failure, in which case anything is possible. Fixing that would probably
be more difficult than fixing the cause of the failure and re-doing the
import.

As I said, it's all speculation. Without input from the OP there's not
much point in continuing this discussion.

Regards,
--
Alban Hertroys
al...@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I:www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

oh yes! You all are exactly right.I exported a table and all nulls
became \N and when i imported it \n remained \N and did not convert to
NULL.So I updates all \N s with '' now.
Thank you all for your input.I got to learn a lot from you.

pc

PS: Please refer to me as she :)

pc wrote:

oh yes! You all are exactly right.I exported a table and all nulls
became \N and when i imported it \n remained \N and did not convert to
NULL.So I updates all \N s with '' now.
Thank you all for your input.I got to learn a lot from you.

Since the empty string is different from NULL, doesn't that mean that the
imported data still differ from the exported?

--
Lew