product version: Oracle 19C
I have a partitioned table myTable with a column definition myCol VARCHAR2(2 BYTE)
Recently some data has been loaded using SQL Loader and during the loading process apparently spaces have been loaded to this column when there is no value. I am trying to treat rows with spaces/blanks as null.
My question: How to find what is contained in the rows that are blank and how to treat those values as null when I select those rows?
When I issue below query I see no rows returned.
select * from myTable where trim(myCol) is null
I am not able to figure out what is contained in those rows. Please see below debug queries I tried -
select '#'||myCol||'#' from myTable ;
output
--##
select length(myCol) from myTable ;
output
--2
select ascii(substr(myCol, 1,1)) as myCol_ascii_Post1 from myTable ;
output
--0
select ascii(substr(myCol, 2,1)) as myCol_ascii_Post2 from myTable ;
--0
select ascii(substr(myCol, 3,1)) as myCol_ascii_Post3 from myTable ;
output
--(null)
select DUMP(myCol) from myTable ;
output
--Typ=1 Len=2: 0,0
select myCol,trim(myCol) as myCol_trim,nvl(trim(myCol),'NULL Value') as myCol_trim_nvl from myTable ;
output -- blank , blank, blank
select count(myCol)
from myTable and trim(myCol) is null;
output
--0
select count(myCol) from myTable and trim(myCol) = '';
output
--0
select myCol from myTable;
output
-- several blank rows
select count(myCol) from myTable and ascii(myCol) = 0 order by 1 desc;
output
--345051 rows
select count(myCol) from myTable and ascii(myCol) > 0 order by 1 desc;
output
-- 0 rows
The characters are not spaces or blanks, and the column itself is not null; it has two null characters. (See ASCII or Unicode.) That's what the dump and ascii calls are showing - character code zero.
So rather than looking for null or spaces, you can look for that pair of characters:
select * from myTable where myCol = chr(0) || chr(0);
or treat the value as null by trimming that character rather than the default space:
select * from myTable where trim(both chr(0) from myCol) is null;
Or ltrim/rtrim with that character.
db<>fiddle demo; but showing an ID instead of the real value because null characters break output there. (They confuse other clients too; you can't copy from SQL Developer properly, for example.)
Assuming you consider the data load to be incorrect, and the column is nullable, you might want to correct the loaded data with:
update myTable set myCol = null where myCol = chr(0) || chr(0);
or delete the rows using the same filter, if they shouldn't have been loaded at all.