How to exclude rows with varchar2 value that is not null, but exports as null

Question

I have some data, which I cannot change, in a varchar2(800) column, which displays in SQL Developer as a null and exports from there as a null, but is not null.

Here's what I tried:

• length(mycol) return 3
• asciistr(mycol) returns null
• substr(mycol, 1, 1) returns null (the other positions return normal letters)
• ascii(substr(mycol, 1, 1)) returns 180, which is a acute accent

I want to not select rows that will export with a null value.

How can I do that?

I tried asciistr(mycol) is not null, but that also filters out text with accented characters that do not export as null. I want to export accented characters, but not when the whole column will be exported as null.

mycol is not null and to_char(mycol) is not null had no effect.

Obviously, I need a solution that will work for any length of input and character weirdness at any position in the field.

Answer 1

Acute accent's ASCII code is 239. Have a look at this example; see if it helps.

Insert some values into a table; one of these is the acute accent:

SQL> create table test (mycol varchar2(10));

Table created.

SQL> insert into test values ('a');

1 row created.

SQL> insert into test values (chr(239));

1 row created.

SQL> select * from test;

MYCOL
----------
a
´

What's stored in there? Pay attention to the DUMP function:

SQL> select mycol, dump(mycol) dmp, ascii(mycol) ascii
  2  from test;

MYCOL      DMP                    ASCII
---------- -------------------- -------
a          Typ=1 Len=1: 97           97
´          Typ=1 Len=1: 239         239

SQL> select * from test
  2  where ascii(mycol) <> 239;

MYCOL
----------
a

SQL>

Check what DUMP returns in your case; maybe there's some more garbage in the column but - if you find out what it really is - you might even be able to UPDATE that column and put a "real" NULL (or something else) in there .