Parsing Field with multiple values

Last Post 13 Jun 2008 07:14 AM by GoodFella3993. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
GoodFella3993
New Member
New Member

--
11 Jun 2008 01:08 PM
I have a field with values consisting of multiple values separated by a Pipe Delimeter (|).

EX: Business Application Initiatives | Testing | Quality | Outsourcing Initiatives | SOA and Web Services Initiatives


I am trying to create a script that will parse out the values in the field and import them into individual records in a new table.

How would I go about doing this?


Thank You,
Marc
GoodFella3993
New Member
New Member

--
13 Jun 2008 07:14 AM
I have been working with this resource: http://sommarskog.se/arrays-in-sql-2000.html


Data Set

1 Testing | Quality | IT Operations Initiatives
2 Business Application Initiatives | Testing
3 Testing | Quality | Change Management
4 Testing | Quality
5 Business Application Initiatives | Testing | Quality | SOA and Web Services Initiatives
6 Business Application Initiatives | Testing | Quality | Production Monitoring | Change Management

Should be ...

1 Testing
1 Quality
1 IT Operations Initiatives
2 Business Application Initiatives
2 Testing
...

So basically, so far I have created a function

CREATE FUNCTION duo_text_split_me(@list ntext,
@delim nchar(1) = N'|')
RETURNS @t TABLE (str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int

SELECT @textpos = 1, @maxlen = 4000 - 2
WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @stoppos = @maxlen - charindex(@delim, reverse(@slice))
INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
SELECT @textpos = @textpos - 1 + @stoppos + 2 -- On the other side of the pipe.
END
INSERT @slices (slice)
VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)

INSERT @t (str, nstr)
SELECT str, str
FROM (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
charindex(@delim, s.slice, N.Number + 1) - N.Number - 1)))
FROM Numbers N
JOIN @slices s ON N.Number <= len(s.slice) - 1
AND substring(s.slice, N.Number, 1) = @delim) AS x

RETURN
END

-----------------------------------------------------------------

And a table (Numbers) that simply lists numbers 1 - 8000

-----------------------------------------------------------------

Then, it seems there is a SP that uses this function:

CREATE PROCEDURE get_company_names_inline @customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@customers) s ON C.CustomerID = s.Value
go

-----------------------------------------------------------------

I don't have alot of experience with advanced functions like this.

Any thoughts?
You are not authorized to post a reply.

Acceptable Use Policy