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 - Follow Up

Enigma (http://sqljunkies.com/weblog/enigma) posted a comment on the first entry in this topic regarding another method for converting a delimited list to a table. I will show an example of it here (formatted for ease of reading):

Given the structures...

CREATE TABLE Tally (ID Int)
CREATE TABLE Quotes (Author Varchar(30), Phrase Varchar(1000))

...where table Tally is a sequential list of numbers from 1 to 8000 (you can use this script to populate it)...

DECLARE @a Int
SET @a = 1
WHILE (@a <=8000)
BEGIN
 INSERT INTO Tally VALUES(@a)
 SET @a = @a + 1
END

...and where Quotes has, let's say, the following data...

INSERT INTO Quotes VALUES('Shakespeare', 'A,rose,by,any,other,name,smells,just,as,sweet')

Then the following query would return each delimited value from the Phrase column as a separate row in the resulting table...

SELECT  NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '')
          AS Word
FROM  Tally
 INNER JOIN Quotes
  ON ID <= Len(',' + Phrase + ',')
   AND  SubString(',' + Phrase + ',' , ID - 1, 1) = ','
   AND  CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0
WHERE  Author = 'Shakespeare'

Wow! Many thanks to Joe Celko for this beauty. I love how this works. This is the beauty of set-based operations. For a full explanation of how this works, go to http://www.sqlteam.com/item.asp?ItemID=2652.

Now, how would we apply this to our situation? Well, I want a UDF that will accept any delimited list and delimiter using join technique shown here. Details will be in the next post...

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