Suggestions for cleaning data

I ran into data that had trailing spaces. I expect it from CHAR fields, but it took me by surprise in a VARCHAR field. The field was an ID Number that was defined as a VARCHAR. I'm thinking that it might be wise to use RTRIM() on all VARCHAR fields early on in the ETL process just to be safe.

I usually do isnull(ltrim(rtrim()),'no_string_supplied') for all varchar fields just in case. And those 'no_strings' are way more often than you'd expect.

I like to treat a empty string '' like a NULL. So I do something like.

Code:

CASE WHEN LEN([FIELD_NAME]) > 0 THEN RTRIM([FIELD_NAME]) ELSE 'Unknown' END

The LEN takes care of trailing spaces on VARCHAR (doesn't count them) and the LEN of a NULL is not > 0 so both scenarios trigger the ELSE.

benlotter wrote:I like to treat a empty string '' like a NULL. So I do something like.

Code:

CASE WHEN LEN([FIELD_NAME]) > 0 THEN RTRIM([FIELD_NAME]) ELSE 'Unknown' END

The LEN takes care of trailing spaces on VARCHAR (doesn't count them) and the LEN of a NULL is not > 0 so both scenarios trigger the ELSE.

Old Oracle habit??? :-)