John Pietrar John Pietrar - 5 months ago 9
SQL Question

Searching for strings in files using sql

I have something like

set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'
SELECT SUBSTRING(@file,CHARINDEX(',',@file)+1, CHARINDEX(',',@file,CHARINDEX(',',@file)+1) -CHARINDEX(',',@file)-1) as My_string


This will print out

My_string
----------------
1| bbbb


How could I make it print?

My_string
------------
1| bbbb
2| cccc
3| dddd
4| eeee

Answer

Try this code.,(Refered from Turning a Comma Separated string into individual rows)

declare @file varchar(max)
set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'

;with tmp(DataItem, Data) 
as (
select LEFT(@file, CHARINDEX(',',@file+',')-1),
    STUFF(@file, 1, CHARINDEX(',',@file+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > '')
select DataItem from tmp