CODEWITHSUNDEEP
sqlsql-server
shashi sampige
shashi sampige

Reputation: 124

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

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

Upvotes: 0

Views: 1850

Answers (4)

ASH
ASH

Reputation: 20302

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.

Upvotes: 0

sebu
sebu

Reputation: 2954

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' ,',' )

Upvotes: 0

shashi sampige
shashi sampige

Reputation: 124

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)

Upvotes: 0

Ven
Ven

Reputation: 2014

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

Upvotes: 1

Related Questions