THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Delimited List to Table

After writing this function, I discovered several months later on the internet a handful of other people had done essentially the same thing, with only slightly varying methodologies. I thought I was being rather novel when I did this not only because of what it did, but also how I used it after the fact.

I'm talking about converting a delimited list into a table without using dynamic executed T-SQL. Here is my version of the UDF that does just that.

CREATE FUNCTION dbo.fnSplit2Table(@sData Varchar(8000), @sDelim Char(1))
RETURNS @tList TABLE (ListValue Sql_Variant)
AS
BEGIN
  DECLARE @sTemp Varchar(1000),
    @nPos Int,
    @nPos2 Int

  IF Len(RTrim(LTrim(@sData))) = 0
    RETURN
 

  SET @nPos = CharIndex(@sDelim, @sData, 1)
 

  IF @nPos = 0
    BEGIN
      SET @sTemp = SubString(@sData, 1, Len(@sData))
      INSERT INTO @tList VALUES(@sTemp)
      RETURN
    END

  IF @nPos = Len(@sData)
    BEGIN
      SET @sTemp = SubString(@sData, 1, Len(@sData) - 1)
      INSERT INTO @tList VALUES(@sTemp)
      RETURN
    END

  SET @sTemp = SubString(@sData, 1, @nPos - 1)
  INSERT INTO @tList VALUES(@sTemp)

  WHILE @nPos > 0
    BEGIN
      SET @nPos2 = CharIndex(@sDelim, @sData, @nPos + 1)
      IF @nPos2 = 0 
        SET @sTemp = SubString(@sData, @nPos + 1, Len(@sData))
      ELSE
        SET @sTemp = SubString(@sData, @nPos + 1, ABS(@nPos2 - @nPos - 1))

      INSERT INTO @tList VALUES(@sTemp)

      SET @nPos = CharIndex(@sDelim, @sData, @nPos + 1)
    END
  RETURN
END

I'm sure this could be optimized in a number of ways, and could also be made to trim extra spaces as well. If you have any suggestions, please add them as comments for one and all to see.

To use it, you simply supply a delimited list as the first parameter and delimiter as the second, as you see here:

SELECT * FROM dbo.fnSplit2Table('A,B,C,D,E,F,G', ',')

This will return a table with a row for each of the delimited values, as shown here:

ListValue
------------
A
B
C
D
E
F
G

In my next post, I will use this function in conjunction with a stored procedure and to show how to “page” data.

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement