Suggestions for cleaning data

View previous topic View next topic Go down

Suggestions for cleaning data

Post  Jeff Smith on Thu Jan 12, 2012 6:24 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Suggestions for cleaning data

Post  ykud on Wed Jan 25, 2012 6:22 am

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.
avatar
ykud

Posts : 12
Join date : 2012-01-16

View user profile http://ykud.com

Back to top Go down

treat '' empty set like NULLs

Post  benlotter on Fri Dec 27, 2013 2:30 pm

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.
avatar
benlotter

Posts : 2
Join date : 2011-08-12
Age : 40
Location : Wisconsin

View user profile http://benjaminlotter.com/

Back to top Go down

Re: Suggestions for cleaning data

Post  ngalemmo on Fri Dec 27, 2013 3:23 pm

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??? :-)
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Suggestions for cleaning data

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum