SQL Server split string with delimiter

Question

I have an input string

100|2|3,101|2|1,103|2|3.

I would like to parse and add this in table having 3 columns so it should go f.x

 col1 col2 col3 
 100   2     3

similar other data separated by comma as record and | as column.

Thanks

nik

Possible duplicate of [How to split a single column values to multiple column values?](http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values) — anatol, Dec 30 '16 at 05:49

mike morris · Answer 1 · 2016-12-30T07:03:49.580

1

Or this way:

SELECT 
    LEFT(value, Charindex('|', value) - 1),
    SUBSTRING(value, Charindex('|', value) + 1,Len(value) - Charindex('|', Reverse(value)) - Charindex('|', value)),
    RIGHT(value, Charindex('|', Reverse(value)) - 1)
FROM
    string_split('100|2|3,101|2|1,103|2|3',',')

edited Dec 30 '16 at 07:03

answered Dec 30 '16 at 06:52

mike morris

174
7

score 0 · Answer 2 · answered Dec 30 '16 at 06:51

Try this way

DECLARE  @TAB TABLE(COLUMN1 INT, COLUMN2 INT, COLUMN3 INT)

DECLARE @STRING VARCHAR(MAX)='100|2|3,101|2|1,103|2|3,'
SELECT @STRING = 'SELECT ' + REPLACE( REPLACE (@STRING, ',','
UNION ALL 
SELECT '),'|',',')

SELECT @STRING = SUBSTRING(@STRING,1,LEN(@STRING)-18)


INSERT INTO @TAB
EXEC(@STRING)

SELECT * FROM @TAB

And the result will be

+---------+---------+---------+
| COLUMN1 | COLUMN2 | COLUMN3 |
+---------+---------+---------+
|     100 |       2 |       3 |
|     101 |       2 |       1 |
|     103 |       2 |       3 |
+---------+---------+---------+

anjali · Answer 3 · 2016-12-30T07:23:10.623

   Declare @temp table(col1 int,col2 int ,col3 int)
   Declare @pos int,@str nvarchar(max),@len int

   Set @str='100|2|3'
   Set @pos=1

Select @len=len(@str)

Insert into @temp
Select substring(@str,@pos,charindex('|',@str,@pos)-1),
       substring(@str,charindex('|',@str,@pos)+1,charindex('|',@str,@pos)-3), 
       substring(@str,charindex('|',@str,charindex('|',@str,@pos)+1)+1,@len)
Select * from @temp

score 0 · Answer 4 · answered Dec 30 '16 at 08:32

 ;WITH tb(s)AS(
        SELECT '100|2|3' UNION
        SELECT '101|2|1' UNION 
        SELECT '103|2|3'
  )
 SELECT PARSENAME(REPLACE(s,'|','.'),3)
       ,PARSENAME(REPLACE(s,'|','.'),2) 
       ,PARSENAME(REPLACE(s,'|','.'),1)  
 FROM tb

(No column name)    (No column name)    (No column name)
100 2   3
101 2   1
103 2   3

SQL Server split string with delimiter

4 Answers4

Linked