Parse delimited values in multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parse delimited values in multiple columns

Author

Topic

darthasshat
Starting Member

17 Posts

Posted - 2008-01-25 : 15:22:19

I have a function I found that will parse a delimited value in a column. It works if it's only one but not if there are multiple columns in the row. Here's the function


DECLARE @CityList varchar(8000), @StateList varchar(8000)
SET @CityList = 'Milwaukee|Chicago|New York|Seattle|San Francisco'
SET @StateList = 'Wisconsin|Illinois|New York|Washington|California'

--declare the delimeter between each City
DECLARE @Delimeter char(1)
SET @Delimeter = '|'

--Parse the string and insert each city into the @tblCity table
DECLARE @tblCity TABLE(State varchar(50), City varchar(50))
DECLARE @State varchar(50)
DECLARE @City varchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@StateList) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @StateList)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@StateList) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
      BEGIN
        SET @State = SUBSTRING(@StateList, 1, @StartPos - 1)
        SET @StateList = SUBSTRING(@StateList, @StartPos + 1, LEN(@StateList) - @StartPos)
      END
    ELSE
      BEGIN
        SET @State = @StateList
        SET @StateList = ''
      END
    INSERT @tblCity (State) VALUES(@State)
END

WHILE LEN(@CityList) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @CityList)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@CityList) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
      BEGIN
        SET @City = SUBSTRING(@CityList, 1, @StartPos - 1)
        SET @CityList = SUBSTRING(@CityList, @StartPos + 1, LEN(@CityList) - @StartPos)
      END
    ELSE
      BEGIN
        SET @City = @CityList
        SET @CityList = ''
      END
    INSERT @tblCity (City) VALUES(@City)
END

--Show all Cities in the @tblCity table
SELECT * FROM @tblCity

Here is the result

State City
Wisconsin NULL
Illinois NULL
New York NULL
Washington NULL
California NULL
NULL Milwaukee
NULL Chicago
NULL New York
NULL Seattle
NULL San Francisco

I want it to return this

State City
Wisconsin Milwaukee
Illinois Chicago
New York New York
Washington Seattle
California San Francisco

Any help in the right direction would be greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-25 : 16:45:08

rather than try to fix that one will this work for you?


DECLARE @CityList varchar(8000), @StateList varchar(8000)
SET @CityList = 'Milwaukee|Chicago|New York|Seattle|San Francisco'
SET @StateList = 'Wisconsin|Illinois|New York|Washington|California'

set nocount on

declare @id int
	,@pos int
	,@val varchar(8000)
	,@c varchar(1)
	,@rowid int

declare @t table (rowid int, city varchar(8000), state varchar(8000))

select	@id = 1
	,@pos = 1
	,@rowid = 1

while @pos <= len(@cityList)
begin
	set @c = substring(@cityList, @pos, 1)
	if @c = '|' or @pos = len(@cityList)
	begin
		if @c <> '|' set @val = coalesce(@val + @c, @c)
		
		if @val is not null insert @t (rowid, city) values (@rowid, @val)
		select @val = null
			,@c = null
			,@rowid = @rowid + 1
	end
	else
	begin
		set @val = coalesce(@val + @c, @c)
	end
	set @pos = @pos + 1
end

select	@id = 1
	,@pos = 1
	,@rowid = 1
	,@val = null

while @pos <= len(@stateList)
begin
	set @c = substring(@stateList, @pos, 1)
	if @c = '|' or @pos = len(@stateList)
	begin
		if @c <> '|' set @val = coalesce(@val + @c, @c)

		if @val is not null update @t set state = @val where rowid = @rowid
		select @val = null
			,@c = null
			,@rowid = @rowid + 1
	end
	begin
		set @val = coalesce(@val + @c, @c)
	end
	set @pos = @pos + 1
end

select city, state from @t order by rowid
EDIT:
tweak to get the last or only value

city          state        
---------------------------
Milwaukee     Wisconsin
Chicago       Illinois
New York      New York
Seattle       Washington
San Francisco California

Be One with the Optimizer
TG

darthasshat
Starting Member

17 Posts

Posted - 2008-01-25 : 17:08:28

That's brilliant!! Thank you so much!

darthasshat
Starting Member

17 Posts

Posted - 2008-01-25 : 17:32:25

One more question, how would I use this in SQL view? I have a list that has values like my example above. Also, I do have some columns that don't need to be parsed. So if it was like this

SET @CityList = 'Milwaukee,Chicago,New York,Seattle,San Francisco'
SET @StateList = 'Wisconsin,Illinois,New York,Washington,California'
SET @Country = 'USA'

How would I get USA to repeat accross all rows? I tried to apply your code and it only shows up on the first row. Thanks again for your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-25 : 18:39:49

quote:

Thanks again for your help!
how would I use this in SQL view?
How would I get USA to repeat accross all rows?

You're welcome.
Your original post said that this was a function. This is sounding more and more like you had a wacky idea and your solving problems as you go

Why don't you give us the big picture of what you are starting with and what your overall objective is. There is probably a better way.

Be One with the Optimizer
TG

darthasshat
Starting Member

17 Posts

Posted - 2008-01-25 : 19:02:51

Sorry about that. I had found the code that I previously submitted and was all giddy to get it working. Ok here goes. We've created an InfoPath application that tracks hazardous waste usage by equipment. The form pushes the data to a sharepoint list. To save time, some repeating elements were combined into a comma delimited field and then pushed to the table. One thing to note is that if there will always be the same amount of delimited values in each field. So if code has 4 items, product name will have 4 items and so forth. Using that example, if there are 4 products delimited in 1 row, I need 4 rows returned with all of the other elements. For instance PreparedBy. I'm hoping to put this in a view so it can be displayed on an aspx page in a grid. Again, thank you very much for your help!

Below is an example record.


ID	EquipmentName	Code	ProductName	MaterialType	InkType	ContainerType	Usage	UOM	Temp	ControlEff	Retention	TotalUsage	TotalVOC	PreparedBy	Location	ReportDate	UsageDate	Rules	Cutoff	Type	FormName
411	V30-1 CPR (D37)	12, 264, 363, 351	Low VOC Roller Blanket Wash, 302 Blue, Hi Speed Process Yellow, Soy 202 Red	blanket wash, ink, ink, ink	n/a, heatset, heatset, soy	Tote, Tote, Can, Tote	10, 10, 323, 10	gal, gal, lbs, gal	Ambient		95%	573.2	167.93	Tom Slick		1/22/2008	1/23/2008	1030, 1113, 1171, and 219		Press	1_2008-01-22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources