Spliting Comma Seperated Values in Sql server 2000

Unfortunately, there is no built-in support for arrays in SQL Server's T-SQL. SQL Server 2000 did add some new datatypes like sql_variant, bigint etc, but no support for the much needed arrays. There are some situations, that require the ability to pass a list of values to a stored procedure. Think about a web page, that lets the user select one or more of his/her previous orders, on submit, retrieves complete information about the selected orders. In this case, passing a list of selected order numbers to the stored procedure, in one go, and getting the results back is more efficient, compared to calling the same stored procedure for each selected order number.

Since, we cannot create arrays of variables or input parameters or columns in T-SQL, we need to look for workarounds and alternatives. Over the years, programmers developed different techniques, some of which are not so efficient, some efficient, but complex. The most popular technique is to pass in a list of values, separated by commas (CSV). With this method, the normal input parameter of the stored procedure receives a list of say, OrderIDs, separated by commas.

In this post I which is taken from the this link you can read this article for further reading, I will try to make a table from the CSV. I have divide code in two parts List 1 contain the declaration of the variable and List 2 contain the execution statements. And at the end full code combining the List 1 and List 2.

Here is the List 1 code which has the declaration of the variables. The @strCommSeparatedValue will contain the Comma separated value I have set the size of the of the variable to 500 and its type is NVARCHAR. Next I have declared the Int type variable named @intRowNumber which is used to assign the ID to each of the comma separated value. After the declaration of the both the variable I have set the value of the both the variables.

Declare @strCommaSeparatedValue NVARCHAR(500)
Declare @intRowNumber INT

Set @strCommaSeparatedValue  ='Item 1,Item 2,Item 3,Item 4,Item 5,Item 6'   
Set @intRowNumber = 1

Declare @tblTemporary Table
(
ItemID   INT,
ItemName NVARCHAR(500)
)

DECLARE @strCurrentItem          NVARCHAR(500),
   @intFirstCommaPosition   INT

SET @strCommaSeparatedValue  = LTRIM(RTRIM(@strCommaSeparatedValue ))+ ','
SET @intFirstCommaPosition   = CHARINDEX(',', @strCommaSeparatedValue , 1)

List 1

Next in the List 1 is the declaration of the table which has only two column the ItemID and the ItemName which are of INT and NVARCHAR type. Which is used to hold the values which are separated by comma and place in this tabel named @tblTemporary. Next I have declare the @strCurrentItem of type NVARCHAR which is used to hold the current item from the CSV. The @intFirstCommaPosition variable is used to hold the position of the first comma from the left side. Now in the next statement which is used to trim the value of the CSV from both side by using the LTRIM which is used to removing leading blanks and RTRIM which is used truncating all trailing blanks and both the LTRIM and RTRIM return the character string and at the end added the last comma in the CSV value so that to access the last item from the CSV. In the next statemetn I have assign the value to the @intFirstCommaPosistion variable by using the CHARINDEX which will returns the starting position of the specified expression in a character string.

Here is the List 2 code which is used separate the CSV value. First the if condition is place to check if there is value in the CSV and user didn't pass only the commas in the string value. In the next statement while loop is place which is used to check the value of the @intFirstCommaPosition, for greater the 0(zero). In the next statement which is use to get the first item from the CSV by using the Left function of the sql server. The left function will returns the left part of a character string with the specified number of characters.

IF REPLACE(@strCommaSeperatedValue, ',', '') <>''
BEGIN
WHILE @intFirstCommaPosition > 0
BEGIN
SET @strCurrentItem = LTRIM(RTRIM(LEFT(@strCommaSeperatedValue, @intFirstCommaPosition - 1)))
IF @strCurrentItem <> ''
BEGIN
 INSERT INTO @tblTemporary (ItemID,ItemName) VALUES (@intRowNumber,@strCurrentItem)
END
SET @strCommaSeparatedValue = RIGHT(@strCommaSeperatedValue, LEN(@strCommaSeperatedValue) - @intFirstCommaPosition)
SET @intFirstCommaPosition  = CHARINDEX(',', @strCommaSeparatedValue , 1)
SET @intRowNumber = @intRowNumber+1
END
END   

SELECT * from @tblTemporary

List 2

After extracting the first element from the CSV , next is to check the current item for the empty string. So if condition is place to check the empty string, so that empty string can't be inserted in the @tblTemporary table. If if condition is true mean the current item is not equal to the empty string then insert it in the @tblTemporary table. Next is to assign the new string to the @strCommaSeparatedValue variable by removing the currently added item in the table. Here Right function is used which returns the right part of a character string with the specified number of characters. Here I have passed the @strCommaSeparatedValue and then then length of the @strCommaSeparatedValue minus the @intFirstCommaPosition, so that the current item is remove which is place before the first comma position. Next assign new value to the @intFirstCommaPosition variable and increment the @intRowNumber.

Note: you can place the @intRowNumber in the if condition where the current item is check for the empty string and then inserted int he @tblTemporary table.So if there is empty string then @intRowNumer is increamented only for the non-empty string values.

Declare @strCommaSeparatedValue NVARCHAR(500)
Declare @intRowNumber INT

Set @strCommaSeparatedValue  ='Item 1,Item 2,Item 3,Item 4,Item 5,Item 6'   
Set @intRowNumber = 1

Declare @tblTemporary Table
(
ItemID       INT,
ItemName NVARCHAR(500)
)

DECLARE @strCurrentItem              NVARCHAR(500),
   @intFirstCommaPosition   INT

SET @strCommaSeparatedValue  = LTRIM(RTRIM(@strCommaSeparatedValue ))+ ','
SET @intFirstCommaPosition   = CHARINDEX(',', @strCommaSeparatedValue , 1)

IF REPLACE(@strCommaSeperatedValue, ',', '') <>''
BEGIN
WHILE @intFirstCommaPosition > 0
BEGIN
SET @strCurrentItem = LTRIM(RTRIM(LEFT(@strCommaSeperatedValue, @intFirstCommaPosition - 1)))
IF @strCurrentItem <> ''
BEGIN
 INSERT INTO @tblTemporary (ItemID,ItemName) VALUES (@intRowNumber,@strCurrentItem)
END
SET @strCommaSeparatedValue = RIGHT(@strCommaSeperatedValue, LEN(@strCommaSeperatedValue) - @intFirstCommaPosition)
SET @intFirstCommaPosition  = CHARINDEX(',', @strCommaSeparatedValue , 1)
SET @intRowNumber = @intRowNumber+1
END
END   

SELECT * from @tblTemporary

Final Code

Above is the full and the final code, you can copy past it and test it yourself. During this I have learned lot of new thing like the LTRIM, RTRIM , LEFT , RIGHT etc regarding the string manipulation.

Reference
1- Passing a list/array to an SQL Server stored procedure
2- String Functions

All and any comments / bugs / suggestions are welcomed!