Strategy to fix data

dimaeuroasia · July 7, 2020, 6:20pm

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

UNI_LIST

yosiasz · July 7, 2020, 6:33pm

please provide the sample data as real usable data , not as embedded picture
What is it you want fixed?

create table #dima(baddata nvarchar(150))

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

dimaeuroasia · July 7, 2020, 6:38pm

Clean data should look like on right hand side.

Thank you.

yosiasz · July 7, 2020, 7:09pm

and now please provide sample data with DDL and DML.

create table #dima
insert into #dima

help us help you.

mike01 · July 7, 2020, 7:09pm

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

dimaeuroasia · July 7, 2020, 7:41pm

Thank you. This is very helpful.

harishgg1 · July 8, 2020, 5:16am

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