CODEWITHSUNDEEP
sqlsql-server
jnoguerm
jnoguerm

Reputation: 147

How to separate a string and insert into table?

My question is that I have a string like this

Red,House|White,Car|Blue,Table

and I want insert this elements in different rows like this

-  Col1   Col2
-  -----------
-  Red    House
-  White  Car
-  Blue   Table

How can I do it?

Upvotes: 0

Views: 77

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I go with using string_split() or a similar string splitter function which you can add to your database. However, I would phrase the final extract logic as:

select left(s.value, v.split - 1),
       stuff(s.value, 1, v.split, '')
from string_split('Red,House|White,Car|Blue,Table', '|') s cross apply
     (values (charindex(',', s.value))) v(split);

Upvotes: 0

hotfix
hotfix

Reputation: 3396

maybe this is what are you looking for.

SELECT Substring(value, 1,Charindex(',', value)-1)  as col1
,  Substring(value, Charindex(',', value)+1, LEN(value)) as  col2
FROM STRING_SPLIT('Red,House|White,Car|Blue,Table', '|')

works since SQL Server 2016

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this query.

DECLARE @str VARCHAR(500) = 'Red,House|White,Car|Blue,Table'
CREATE TABLE #Temp (tDay VARCHAR(100))
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
    IF CHARINDEX('|',@str) > 0
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX('|',@str))
    ELSE
        BEGIN
        SET  @TDay = @str
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay)
 SET @str = REPLACE(@str,@TDay + '|' , '')
 END

 SELECT * 
 FROM #temp

 SELECT tday,
    PARSENAME(REPLACE(tday,',','.'),2) 'Col1' ,
    PARSENAME(REPLACE(tday,',','.'),1) 'Col2'
FROM #temp

You can check the live demo Here.

Upvotes: 0

Related Questions