Split comma delimitted string with T-SQL?

Databases
1

I am trying to write a SQL Server stored procedure that splits a comma delimitted string into multiple arrays.
Eg.
From:
str = “12, 1, 5”
to:
arr(0) = 12
arr(1)=1
arr(2)=5

Does anyone know if T-SQL has a function similar to vb’s “split”?

2

I don’t believe arrays are supported in sql so there’s no equivilant of VB’s Split function. However, these articles might be of help with dealing with comma delimited strings.

3

He is talking about the Transact SQL programming language that comes with SQL Server and Sybase. It is above and beyond what is the SQL Standard and used to create stored procedures on those systems.

I know it supports it, I just can’t recall how at the moment since it has been a few years since I worked with T-SQL.

4

I was referring to T-SQL Wayne but from my knowledge there isn’t any equivalent of the Split function or support for arrays in T-SQL.

I make no claims to being a T-SQL expert though so I could well be wrong.

5

You’d probably have to do something like this (C developers know and hate this ;)):



CREATE TABLE #destination_table(
  number INT
)

DECLARE @position INT
DECLARE @source_string VARCHAR( 1000 )

SET @source_string = "1,2,3,4,5,6"

SET @position = CHARINDEX( ",", @source_string )

WHILE @position <> 0
BEGIN

  -- You'll probably have to CAST the LEFT since number is an INT,
  -- or you can change number in #destination_table to be a varchar
  INSERT INTO #destination_table VALUES( LEFT( @source_string, @position ) )

  -- I can't recall if STUFF actually modifies @source_string,
  -- so the assignment may be unnecessary
  SET @source_string = STUFF( @source_string, 0, @position, NULL )

END