CODEWITHSUNDEEP
sql-serversql-server-2008-r2
DPA
DPA

Reputation: 31

Convert comma delimited string to table or array in sql server 2008 without using dbo.split

How to convert comma delimited string to table or array in sql server 2008 without using dbo.split function because the system doesn’t support this function?

Eg of string: ’12,14,15’ Change this to 

*Table column*
12
14
15

Or array=[12,14,15]

I would like to insert comma separated string values into a table ultimately. Thanks

Upvotes: 2

Views: 21342

Answers (3)

DPA
DPA

Reputation: 31

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

Upvotes: -1

J-D
J-D

Reputation: 1575

If you don't want to use split, you can create your own custom function to achieve this.

Please follow below stack question for same.

How to convert comma separated NVARCHAR to table records in SQL Server 2005?

Below link covers all possible way to achieve this.

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

dbo.split is probably user defined function, so you need to define it. Otherwise you can use XML + CROSS APPLY:

Demo

DECLARE @string NVARCHAR(100) = '12,14,15'


;WITH cte AS
(
   SELECT 
    
      CAST('<XMLRoot><RowData>' + REPLACE(t.val,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
      FROM (SELECT @string) AS t(val)
)
SELECT 
    m.n.value('.[1]','varchar(8000)')
FROM cte
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Upvotes: 3

Related Questions