To split data(text) in a particular column with required condition in MS SQL

Question

I have a table column which contains data with commas(no. of commas are fixed). I want to split the data and extract the data according to the comma. For example:

Select column_name From table-> 

->column_name
->adc,efg,ghi,ijk

I want to extract every text separated by the comma(till the last text). For example i need :

1)adc
2)efg
3)ghi
4)ijk

I found that it being done in PostGre, but cannot find in MS-SQL. I PostGre the following code was given:

select a[1], a[2], a[3], a[4], a[5], a[6]
from (
select regexp_split_to_array(column_name, ',')
from your_table

) as dt(a)

Similar question here : http://stackoverflow.com/questions/2647/split-string-in-sql Answer is to (ab)use Parsename or Patindex. Another option entirely is to use the CLR - MsSql's string manipulation is generally pretty poor. — StuartLC, Jul 03 '13 at 06:14

score 2 · Answer 1 · answered Jul 03 '13 at 06:11

Try this one -

DECLARE @temp TABLE (id NVARCHAR(50))
INSERT INTO @temp (id)
VALUES ('adc,efg,ghi,ijk')

SELECT  id = ROW_NUMBER() OVER (PARTITION BY t.string ORDER BY 1/0)
     , name = 
        SUBSTRING(
              t.string
            , n.number + 1
            , ABS(CHARINDEX(',', t.string, n.number + 1) - n.number - 1))
FROM (
     SELECT string = ',' + t.id
     FROM @temp t
) t
CROSS JOIN [master].dbo.spt_values n
WHERE n.[type] = 'p'
    AND n.number <= LEN(t.string)
    AND SUBSTRING(t.string, n.number, 1) = ','

To split data(text) in a particular column with required condition in MS SQL

1 Answers1