SQLTeam.com | Weblogs | Forums

Strategy to fix data

Can you please let me know what would be the strategy to fix following data in a table?

UNI_LIST

  1. please provide the sample data as real usable data , not as embedded picture
  2. What is it you want fixed?
create table #dima(baddata nvarchar(150))

insert into #dima
select '070 - Univeristy of' union
select
etc

Clean data should look like on right hand side.

Thank you.

and now please provide sample data with DDL and DML.

create table #dima
insert into #dima

help us help you.

Looks like this is what you are looking for, but pictures don't help. DDL and sample data make it easier for us

create table #dima(baddata nvarchar(150))

insert into #dima
select '070 - Univeristy of Ottawa - Ontario' union all
select 'ON099 University Outside N. America' union all
select 'ON094 York University' union all
select '160 - York University' union all
select '130 - The University of Western Ontario - Ontario' union all
select 'ON098 USA Universities'

Select baddata ,
		Ltrim(Rtrim(Substring(BadData, Case when CharIndex('-',BadData) > Charindex(' ', BadData) then CharIndex('-',BadData) else CharIndex(' ',BadData) end + 1, len(BadData))))
from #dima

Thank you. This is very helpful.

hi

Looking at the data .. i notice a pattern .. if the data always is in this same way ..

simple solution

select 
   substring(baddata,6,len(baddata)) 
from 
   dima

1 Like