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
-
1Possible duplicate of How to split a single column values to multiple column values?anatol– anatol2016-12-30 05:49:40 +00:00Commented Dec 30, 2016 at 5:49
Add a comment
|
4 Answers
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',',')
Comments
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 |
+---------+---------+---------+
Comments
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
Comments
;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
