Split comma delimited string and insert to a table (int)

Question

i have a table with 3 columns (First_ID,Second_ID,Third_ID) all columns are int columns.

Now I have 3 values, first and third values are int values (1 and 0), the second value is a comma delimited string ('188,189,190,191,192,193,194')

what should be my approach to populate the table like the bellow:

I have tried different ways but could not get it to work as i want.

Thanks in advance

possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — M.Ali, May 03 '14 at 00:29
i apologize for my ignorance. i googled that and im using a split function but im having difficulties populating the table — user3430556, May 03 '14 at 00:30
im using this function http://stackoverflow.com/questions/6354742/insert-into-table-from-comma-separated-varchar-list — user3430556, May 03 '14 at 00:35
Where do you have these comma deliminated list of values in a table ?? — M.Ali, May 03 '14 at 00:39
im sending them from my php server. so basically im trying to create an SP that i can call to populate the table — user3430556, May 03 '14 at 00:40
see the answer below and if it helped please accept it as your answer thank you. — M.Ali, May 03 '14 at 00:51

score 24 · Answer 1 · edited Jun 22 '15 at 12:10

24

Better use XML for this,

Declare @Var nvarchar(MAX)

Set @Var ='188,189,190,191,192,193,194'

DECLARE @XML AS XML

DECLARE @Delimiter AS CHAR(1) =','

SET @XML = CAST(('<X>'+REPLACE(@Var,@Delimiter ,'</X><X>')+'</X>') AS XML)

DECLARE @temp TABLE (ID INT)

INSERT INTO @temp

SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('X') AS T(N)

SELECT * FROM @temp

edited Jun 22 '15 at 12:10

bummi

27,123
14
62
101

answered Jun 22 '15 at 11:43

Jom George

1,107
10
21

Isn't XLM slower than VARCHAR? – Marcello Miorelli Dec 12 '16 at 15:00
1

Sorry for delay. XML type may be stored little bit slower due to xml validation. – Jom George Jan 06 '17 at 08:55
2

Works perfectly with no need to create database functions, so good for once-off tasks – geographika Feb 24 '17 at 13:21

score 16 · Accepted Answer · edited May 23 '17 at 12:26

16

Using the Split() function you have mentioned in comments,

-- Variable holding comma separated values
DECLARE @Var VARCHAR(4000);
SET @Var =  '188,189,190,191,192,193,194'

-- Test Target Table
DECLARE @Target_Table TABLE  (First_ID INT,Second_ID INT,Third_ID INT) 

-- Insert statement
INSERT INTO @Target_Table
SELECT 1, CAST(Items AS INT) , 0 
FROM  dbo.Split(@Var, ',')  

-- Test Select
SELECT * FROM  @Target_Table

Result Set

╔══════════╦═══════════╦══════════╗
║ First_ID ║ Second_ID ║ Third_ID ║
╠══════════╬═══════════╬══════════╣
║        1 ║       188 ║        0 ║
║        1 ║       189 ║        0 ║
║        1 ║       190 ║        0 ║
║        1 ║       191 ║        0 ║
║        1 ║       192 ║        0 ║
║        1 ║       193 ║        0 ║
║        1 ║       194 ║        0 ║
╚══════════╩═══════════╩══════════╝

edited May 23 '17 at 12:26

Community

1
1

answered May 03 '14 at 00:50

M.Ali

67,945
13
101
127

7

Split is not a function in SQL Server. Using SQL Server 2012. Do i need to do any special thing – Avinash Jain Aug 31 '16 at 12:11
6

There is no SQL Server function split() – defines Nov 10 '16 at 14:44
To the people commenting (correctly) that SQL Server doesn't contain a Split() function; this answer is suggesting OP create a dbo.split function as seen in the question being linked to. This answer is showing OP how to use the split function created in that other question to handle their slightly more complicated situation. – Keiki Oct 12 '17 at 19:28
For SQL Server 2012 or above,can use "dbo.SplitString()". works similar to split() function. :) – siddaramesh Dec 17 '18 at 14:04

score 6 · Answer 3 · answered Jan 31 '18 at 13:08

6

From SQL server 2016 onwards you can use this function string_split

DECLARE @Var VARCHAR(4000);
SET @Var =  '188,189,190,191,192,193,194'

SELECT 1 as First_ID, value as Second_ID ,0 as Third_ID FROM string_split(@Var,',')

answered Jan 31 '18 at 13:08

Rolwin Crasta

4,219
3
35
45

I am using SQL Server 2017 but `string_split` is not working. – ankur pramanik Oct 07 '21 at 06:38

score 1 · Answer 4 · answered Apr 30 '19 at 13:25

DECLARE @Table VARCHAR(100)='AAA,BBB,CCC,DDD'
IF OBJECT_ID('[Comma_Split]') IS NOT NULL
DROP TABLE [Comma_Split]
CREATE TABLE [Comma_Split](ID INT IDENTITY(1,1),COL VARCHAR(100))
while LEN(@Table)>0
BEGIN
DECLARE @COMMA INT= CHARINDEX(',', @Table)
IF @COMMA=0 SET @COMMA=LEN(@Table)+1
INSERT INTO [Comma_Split]
SELECT SUBSTRING(@Table,1,@COMMA-1)
SET @COMMA=@COMMA+1
SET @Table=SUBSTRING(@Table,@COMMA,LEN(@Table))
END

Split comma delimited string and insert to a table (int)

4 Answers4

Linked