2

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
DPA
  • 31
  • 1
  • 1
  • 7
  • 2
    This is a duplicate of about 1,000 questions on Stackoverflow, but I think the following series of articles answers the question than any on here - [Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up) and [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) – GarethD Oct 01 '15 at 13:14
  • Why don't you want to use the user defined function to split your string? – SubqueryCrunch Oct 01 '15 at 13:14
  • In the long run it would be a LOT better if you normalized your data instead of sticking delimited strings into a single intersection of your table. This violates 1NF in relational theory and causes nothing but pain and performance issues. – Sean Lange Oct 01 '15 at 13:37

3 Answers3

3

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)
Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • A very strong caveat with the XML approach is it should only be used if you can **guarantee** that your input string does not contain any illegal XML characters. Also that the strings to split are less than 10,000 characters, after this is drops off significantly in terms of performance – GarethD Oct 01 '15 at 13:23
  • @GarethD With numbers it is ok, with text I will first sanitize input. The general rule is that data should be atomic, CSV in text column isn't so they are always tradeoffs – Lukasz Szozda Oct 01 '15 at 13:24
0

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

Community
  • 1
  • 1
J-D
  • 1,575
  • 1
  • 11
  • 22
  • The first link you posted here has about the worst possible way of splitting strings, a while loop. The second link has a number of other options but they also are not very efficient. See the links posted by GarethD in the comments of the original question for much better options. – Sean Lange Oct 01 '15 at 13:36
-1
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
aditya shrivastava
  • 724
  • 1
  • 8
  • 24
DPA
  • 31
  • 1
  • 1
  • 7
  • 3
    When you have a table valued function with multiple statements the performance is going to suffer horribly. Add to that a while loop and you have one of the worst performing splitters found anywhere around the internet. – Sean Lange Oct 01 '15 at 13:34