March 8, 2018 at 8:38 am
I have 149 tables in our DBs with State column. Values I see in that State column are like MI, Michigan, ILL, IL, Illinois etc., so there is no consistency on how we store the data. Even the column name is like State, STATE, state. This is what I used to build a sqlselect 'select top 5 * from '+s.name+'.'+t.name+' where DATALENGTH('+c.name+')>3'
from sys.schemas s
INNER JOIN sys.tables t on s.schema_id=t.schema_id
INNER JOIN sys.columns c on t.object_id=c.object_id
where c.name = 'State'
Now I am trying to find all the records of all state where length of a state column is more than 3 character long so I can change it to use abbreviation but when I run the below query, I am still getting OR or WI. select top 5 * from DBQ412v3 where DATALENGTH(STATE)>3
March 8, 2018 at 8:43 am
What's the data type of the State column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2018 at 8:57 am
GilaMonster - Thursday, March 8, 2018 8:43 AMWhat's the data type of the State column?
It is varchar(50). I am guessing that the ideal situation would have been CHAR(2) and the ability to redirect rows during ETL process but unfortunately that's not the case.
March 8, 2018 at 9:55 am
You could try something like this:
select 'select top 5 * from '+s.name+'.'+t.name+' where '+c.name+' NOT LIKE ''__'''
from sys.schemas s
INNER JOIN sys.tables t on s.schema_id=t.schema_id
INNER JOIN sys.columns c on t.object_id=c.object_id
where c.name = 'State'
Or use the LEN() function that would ignore trailing spaces
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply