question

TSQL is not the best for string manipulation, but it can be done, althought saying that, this little piece of code ramped up my CPU for the 10 secs it ran! Let me walk you through what this is doing. The first part uses a Numbers table [ http://www.sqlservercentral.com/articles/T-SQL/62867/] to split the data on ' ' - I materialize this into a temporary table as trying to do it entirely in CTEs was giving an error (the optimizer will expand out the code and some short cut was throwing out the string manipulation). declare @SampleTable table (id int identity(1,1), SampleData varchar(max)) insert into @SampleTable select 'X1-200'; insert into @SampleTable select 'X2-1000 N7'; insert into @SampleTable select 'V2 B5 N6 N7 M5 G3'; insert into @SampleTable select 'G5 V2-500 B-700'; select st.id, substring(' '+st.SampleData+' ',N+1, charindex(' ', ' '+st.SampleData+' ',N+1)-N-1) as value into #elements from @SampleTable st join DBA.dbo.Numbers N on N.n < len(' '+st.SampleData+' ') and substring(' '+st.SampleData+' ',N,1) = ' '; So the result of this is a data set id value ----------- ------------- 1 X1-200 2 X2-1000 2 N7 3 V2 3 B5 3 N6 3 N7 3 M5 3 G3 4 G5 4 V2-500 4 B-700 then the parts CTE splits out the amounts from the textual values with parts as ( select element.id, case when charindex('-',element.value) > 0 then substring(element.value, 0, charindex('-',element.value)) else element.value end TxtPart, case when charindex('-',element.value) > 0 then substring(element.value,charindex('-',element.value)+1, len(element.value)) else null end AmtPart from #elements element ) if you could look into the CTE at this point, the data now looks like id TxtPart AmtPart ----------- ---------- --------- 2 X2 1000 4 G5 NULL 4 V2 500 2 N7 NULL 4 B 700 1 X1 200 3 V2 NULL 3 B5 NULL 3 N6 NULL 3 N7 NULL 3 M5 NULL 3 G3 NULL so we've isolated all the text values from the values that will be the amounts. The next CTE uses the XML concatenation trick to build the textual values back up into a string , AllEndCD as ( select distinct id , (select ' '+p1.TxtPart from parts p1 where p1.id = p2.id for xml path ( '' ) ) END_CD from parts p2 ) id END_CD ----------- ----------------------- 1 X1 2 X2 N7 3 V2 B5 N6 N7 M5 G3 4 G5 V2 B and also get the amount values , AllAmts as ( select id , p2.AmtPart from parts p2 ) id AmtPart ----------- ----------- 1 200 4 500 4 700 2 1000 Then we outer join these 2 last CTEs together select isnull(AllEndCD.END_CD, AllEndCD.END_CD) as END_CD, AllAmts.AmtPart as END_AMT from AllEndCD left join AllAmts on AllAmts.id = AllEndCD.id order by AllEndCD.id You could possibly make this code shorter, but I've made it step by step just to demonstrate the process. Full code: if object_id('tempdb..#elements','U') is not null drop table #elements declare @SampleTable table (id int identity(1,1), SampleData varchar(max)) insert into @SampleTable select 'X1-200'; insert into @SampleTable select 'X2-1000 N7'; insert into @SampleTable select 'V2 B5 N6 N7 M5 G3'; insert into @SampleTable select 'G5 V2-500 B-700'; select st.id, substring(' '+st.SampleData+' ',N+1, charindex(' ', ' '+st.SampleData+' ',N+1)-N-1) as value into #elements from @SampleTable st join DBA.dbo.Numbers N on N.n < len(' '+st.SampleData+' ') and substring(' '+st.SampleData+' ',N,1) = ' '; with parts as ( select element.id, case when charindex('-',element.value) > 0 then substring(element.value, 0, charindex('-',element.value)) else element.value end TxtPart, case when charindex('-',element.value) > 0 then substring(element.value,charindex('-',element.value)+1, len(element.value)) else null end AmtPart from #elements element ) , AllEndCD as ( select distinct id , (select ' '+p1.TxtPart from parts p1 where p1.id = p2.id for xml path ( '' ) ) END_CD from parts p2 ) , AllAmts as ( select id , p2.AmtPart from parts p2 where p2.AmtPart is not null ) select isnull(AllEndCD.END_CD, AllEndCD.END_CD) as END_CD, AllAmts.AmtPart as END_AMT from AllEndCD left join AllAmts on AllAmts.id = AllEndCD.id order by AllEndCD.id returns END_CD AmtPart -------------------- -------- X1 200 X2 N7 1000 V2 B5 N6 N7 M5 G3 NULL G5 V2 B 500 G5 V2 B 700 (5 row(s) affected)