Comma Delimeted Value - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Comma Delimeted Value

Author

Topic

NUStudent
Starting Member

3 Posts

Posted - 2010-07-21 : 12:52:40

I am attempting to separate a comma delimited value and change the numerical representation of the value to the corresponding text description and replace the comma with the text " Or ".

example:

Input of the value is 2,3
2 = Description 1
3 = Description 2

I want the end result to show: Description 1 or Description 2

basically have a function iterate through the varchar ("2,3"). Retrieve the actual text description for the identifier 2 (Description 1) and then iterate though the varchar again to grab the next value in the comma delimited value (in this case the 3) and grab the 3's description (Description 2) and then put both of those together to create: "Description 1 or Description 2"

I hope this makes some kind of sense.

So far I have this code:

declare @string varchar(500)
set @string = '2,3'
declare @docdesc varchar(max)

declare @pos int
declare @piece varchar(500)

if RIGHT(rtrim(@string), 1) <> ','
Set @string = @string + ','

Set @pos = PATINDEX('%,%', @string)
while @pos <> 0
begin
set @piece = LEFT(@string, @pos - 1)
set @docdesc = Select Description From Description table
Where DescriptionID = @piece
Set @docdesc = docdesc + ' or '
set @string = STUFF(@string, 1, @pos, '')
set @pos = PATINDEX('%,%', @string)
end

print @docdesc

I want this to be in a function and I am unsure as to how to accomplish this. The output I need, just to summarize, will change from 2,3 to Description 1 or Description 2. I have a table with the 2 and 3's descriptions

- NUStudent

Happy Scripting :-)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 13:28:22

where is 2,3 coming from? I assume a UI and is it passed as a paramater or is it somewhere in a field? if you answer these question the answer is very easy

If you don't have the passion to help people, you have no passion

NUStudent
Starting Member

3 Posts

Posted - 2010-07-21 : 13:32:44

quote:
Originally posted by yosiasz

where is 2,3 coming from? I assume a UI and is it passed as a paramater or is it somewhere in a field? if you answer these question the answer is very easy

If you don't have the passion to help people, you have no passion

the 2,3 is coming from another table with that value in a specific column. Example:

Select Member_Name, DisplayDescriptions(DescriptionID)
From tbl_MemberInfo

this is assuming the function's name is DisplayDescriptions()

- NUStudent

Happy Scripting :-)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 13:55:47

you will need the function called ParseValues search for it in this forum. try this changing table variables to your tables



declare @Description table(descriptionid int, description varchar(50))

Insert into @Description 
Select 1,'Meow'union all
Select 2,'Here kitty' union all
Select 3,'not on the carpet!'

declare @memberInfo table(descriptionids varchar(50))

Insert into @memberInfo
Select '2,3' union all
Select '4,5'
declare @mint varchar(500)



SELECT DISTINCT @mint =

       ( SELECT descriptionids + ',' 

           FROM @memberInfo p2

            FOR XML PATH('') ) 

      FROM @memberInfo 

Select * 
  from dbo.ParseValues(@mint) 

SELECT a.* 
FROM @Description a
INNER JOIN (Select val FROM dbo.ParseValues(@mint)) b
ON a.descriptionid = b.Val

happy coding

If you don't have the passion to help people, you have no passion

NUStudent
Starting Member

3 Posts

Posted - 2010-07-21 : 15:34:35

Okay, that works for what I already have accomplished which is taking the value "2,3" and separating it to output like:

2
3

keep in mind that the true values going into the " Temp " table would look more like this

Description 2
Description 3

Keeping in mind:

2 = Description 2
3 = Description 3

So now that we have our " Temp " table with the resulting descriptions from the variable being passed in question ( remember that the variable is "2,3") I want to display the table back as a result like this:

Description 2, Description 3

Assuming we are working with a table with the following structure:

MemberName DescriptionIDs
Member1 2,3
Member2 2,7
Member3 3

We Should get an end result from this table (lets call this table Member_Info) as the following:

MemberName Descriptions
Member1 Description 2, Description 3
Member2 Description 2, Description 7
Member3 Description 3

With a select statement calling the function:

Select MemberName, DisplayDescriptions(DescriptionIDs) as Descriptions

Does that make a better explanation of what I am attempting to do? If not, I am not sure how else to explain :-)

Thanks in advance for your help

- NUStudent

Happy Scripting :-)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 16:03:32

try this


declare @Description table(descriptionid int, descr varchar(50))

Insert into @Description 
Select 1,'Description 1'union all
Select 2,'Description 2' union all
Select 3,'Description 3' 
declare @memberInfo table(descriptionids varchar(50))

Insert into @memberInfo
Select '2,3' union all
Select '4,5'
declare @mint varchar(500)



SELECT DISTINCT @mint =

       ( SELECT descriptionids + ',' 

           FROM @memberInfo p2

            FOR XML PATH('') ) 

      FROM @memberInfo 

SELECT DISTINCT	
		STUFF((SELECT ',' + s2.descr 
		         FROM @Description AS s2 
		         INNER JOIN (Select val FROM dbo.ParseValues(@mint)) b
                     ON s2.descriptionid = b.Val FOR XML PATH('')), 1, 1, '') AS jaba
FROM		@Description AS s1

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources