asked on

t-sql to parse one column

what is the best way to extract data from this column? There could be one or two or three items concatenated with : as a separator. here is sample data:

value = 29826 : 59 : 1 or
value= 29999 : : 1 or
value=29580 : RT : 1 or
value=73560: 59/RT: 1

I need to extract the values from this one column into three separate columns.
thnx,

Lowfatspread

try this ... as long as the initial value data inot longer than 2047 characters...

Select columnlist, coalesce([1],'') as columnname1,coalesce([2],'') as columname2,coaleasce([3],'') as columnname3
from (
select x.*,substring(x.[value]+':',v.number,charindex(':',x.[value]+':',v.number)-v.number) as part
,row_number() over (partition by x.primarykeycolumns order by v.number) as rn
from master.dbo.spt_values as v
cross join YOURTABLE as X
Where v.type='p'
and v.number between 1 and len(x.[value])
and substring(':'+x.[value],v.number,1)=':'
) as P
pivot (max(part) for rn in ([1].[2],[3])) as pvt
order by ....

if the value column can contain nulls then specify yourtable as

(select columnlist,coalesce([value],'') as [value] from yourtable) as X

if your table primary key is multiple columns

then (partition by pk1,pk2,pk3 order by ...

ASKER CERTIFIED SOLUTION