Recognizing "null bytes" in a varchar field
Posted: Mon Nov 16, 2009 1:57 pm
Had an interesting issue this morning and looking for some advice. Client supplied flat file has quote-unquote "junk" in several fields that really should be empty. And since they're not empty, they pass the "null or empty" sniff test that would normally get them excluded from the Oracle load which promptly goes all ker-blooey when it declares them as nulls that have no business being in a field that doesn't allow them.
Specifically, the "junk" looks to me like "low values" from my old COBOL days and are what one DBA on the thread described as "a series of 8 bits of which no bits are set". For those familiar with the Oracle function 'DUMP' it dumps as:
Type=1 Len=5: 0,0,0,0,0
What's the best way to detect a value like this? Convert all of the CHAR(0) values to '' and then check for empty?
Specifically, the "junk" looks to me like "low values" from my old COBOL days and are what one DBA on the thread described as "a series of 8 bits of which no bits are set". For those familiar with the Oracle function 'DUMP' it dumps as:
Type=1 Len=5: 0,0,0,0,0
What's the best way to detect a value like this? Convert all of the CHAR(0) values to '' and then check for empty?
