Thread: query to match '\N'
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' ; both return 0 rows.Could some one please tell me the right query? Thanks Priya
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)
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 //
wow! works for me! Thank you !!
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