How to insert string value into SQL Server table by separating by comma

Question

int QcEditorId = 21
string Freelancer = '2,3,4,5'

I want to insert this value into the SQL Server table like

QcEditorId  Freelancer
----------------------
  21           2
  21           3
  21           4
  21           5

Please help me with a query or stored procedure

Where are these values coming from? How many values do you expect to insert at once? what is the version of sql server you are working with? — Zohar Peled, Apr 26 '18 at 08:55
Look at [this](https://stackoverflow.com/questions/14811316/separate-comma-separated-values-and-store-in-table-in-sql-server). — Sandeep, Apr 26 '18 at 08:56
There are far better answers available now than that accepted solution @Sandeep (which uses as `WHILE` loop). I'm sure, however, if the OP took the time to use a Search Engine (for example, [Google](http://google.com), [Bing](http://bing.com), [Yahoo](http://yahoo.com)) they would find an abundance of dataset approaches; or even find the inbuilt function to SQL Server (which'll be relevant to them if they are using SQL Server 2016+). Separating a delimited is probably one of the most frequent questions on SO in regards to SQL, so the OP should make some effort themselves first. :) — Thom A, Apr 26 '18 at 09:04
@Larnu I somewhat agree. However, when SQL Server version is not mentioned, then we can always go with plain old method of doing this. — Sandeep, Apr 26 '18 at 09:14
@Sandeep there are still dataset approaches available prior to SQL Server 2016; of which almost all of them will work with SQL Server 2008+. A `WHILE` loop is the worst solution here though. — Thom A, Apr 26 '18 at 09:16
@Sandeep even in sql server 2000 you don't need to use a while loop to split a string. There are better solutions for that. — Zohar Peled, Apr 26 '18 at 09:37
Downvote reasons: No research, no attemp, no mcve, not enough information, not responding to comments (or answers). I can probably come up with more reasons to downvote this question. — Zohar Peled, Apr 26 '18 at 09:38

score 1 · Answer 1 · answered Apr 26 '18 at 09:16

String_split starting in SQL server 2016 have ability to split your strings

Example:

 declare @table table (QcEditorId int, Freelancer varchar (100))

    insert into @table
    select 21, '2,3,4,5'
    declare @freelancer varchar(100)
    = (select freelancer from @table)

    select QcEditorId,x.value Name  from @table
    cross apply(
    select *  from string_split(@freelancer,',') ) x
       -- Use this function to split strings and , as delimiter

-- or for previous versions, create a table valued function , a lot ----available in web

 Example:

    CREATE FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX) )
    RETURNS
     @returnList TABLE ([Name] [nvarchar] (500))
    AS
    BEGIN

     DECLARE @name NVARCHAR(255)
     DECLARE @pos INT

     WHILE CHARINDEX(',', @stringToSplit) > 0
     BEGIN
      SELECT @pos  = CHARINDEX(',', @stringToSplit)  
      SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

      INSERT INTO @returnList 
      SELECT @name

      SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
     END

     INSERT INTO @returnList
     SELECT @stringToSplit

     RETURN
    END

Solution:

declare @table table (QcEditorId int, Freelancer varchar (100))

insert into @table
select 21, '2,3,4,5'
declare @freelancer varchar(100)
= (select freelancer from @table)

select QcEditorId,x.Name  from @table
cross apply(
select *  from [dbo].[SplitString](@freelancer) ) x

I'd, personally, suggest using a dataset approach, and an inline-table function. Like i said in the comments, there is no reason to use a `WHILE` loop here, which will be inherently slow, and multi statement functions are much slower than inline functions as well. — Thom A, Apr 26 '18 at 09:18

score 0 · Answer 2 · answered Apr 26 '18 at 09:31

0

DECLARE @FLXML AS XML 
SET @FLXML = cast(('<a>'+replace(@FreelancerId,',' ,'</a><a>')
            +'</a>') AS XML)
INSERT INTO [QMT_UserMaping](QcEditor_ID,Freelancer_ID)
SELECT @QCId,A.VALUE('.', 'varchar(max)')
    FROM @FLXML.nodes('a') AS FN(a)

answered Apr 26 '18 at 09:31

shashi sampige

115
4
15

You can see my given answer, if you want to insert data to table instead of using XML. Thanks. – sebu Apr 26 '18 at 09:38

sebu · Answer 3 · 2018-04-26T09:43:32.690

SQL User Defined Split Function

ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX)) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

INSERT Command

INSERT INTO YourTable(QcEditorId,Freelancer) 
SELECT 21,splitdata FROM [dbo].[fnSplitString]('2,3,4,5' ,',' )

STRING_SPLIT (MSSQL Server 2016)

no need custom user defined function

INSERT INTO YourTable(QcEditorId,Freelancer) 
SELECT 21,value FROM STRING_SPLIT('2,3,4,5' ,',' )

score 0 · Answer 4 · answered May 05 '18 at 02:02

I have a SQL Table like this:
| SomeID         | OtherID     | Data
+----------------+-------------+-------------------
| abcdef-.....   | cdef123-... | 18,20,22
| abcdef-.....   | 4554a24-... | 17,19
| 987654-.....   | 12324a2-... | 13,19,20
Is there a query where I can perform a query like SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......' that returns individual rows, like this?
| OtherID     | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19
Basically split my data at the comma into individual rows?
create table Testdata(SomeID int, OtherID int, Data varchar(max))
insert Testdata select 1, 9, '18,20,22'
insert Testdata select 2, 8, '17,19'
insert Testdata select 3, 7, '13,19,20'
insert Testdata select 4, 6, ''
--The query
;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem
from tmp
order by SomeID
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option
Output
SomeID  OtherID  DataItem
1       9        18
1       9        20
1       9        22
2       8        17
2       8        19
3       7        13
3       7        19
3       7        20
4       6   
9       11       1
9       11       2
9       11       3
9       11       4
etc.

How to insert string value into SQL Server table by separating by comma

4 Answers4