October 8, 2009 at 9:39 am
Here's what I'm trying to do. I have a field in the database that stores up to 9 comma-separated values (e.g. W,I,P,G) and I need to create a function that will lookup each code in the comma-separated list and return a comma-separated list of the full code value. The codes and full code names are stored in a lookup table in the database.
Example:
code name
----- ------
W Way Too Much
I Internal
P Public
G Gone
So if the value stored in the database table is "W,I,P,G" then I need some way within SQL to get the name for each code stored in a comma-separated list. I would like to retrieve this list within SQL for display. The tricky thing is the list of codes passed to the function will be dynamic... maybe the database value will be one code and maybe it will be 8 codes (separated by commas).
Is a user-defined function the way to go or something different? I appreciate any help!
October 8, 2009 at 10:05 am
I would use the following in a stored procedure:
--Use array as a stored procedure argument
DECLARE @Array VARCHAR(MAX)
SET @Array = 'val1|val2|val3|val4|val5|FOO|BAR'
SET NOCOUNT ON
DECLARE
@Delimiter CHAR(1),
@CharPos INTEGER,
@ArrayValue VARCHAR(255)
SET @Delimiter = '|'
SET @CharPos = 1
SET @Array = @Array + @Delimiter
DECLARE @ArrayTable TABLE
(
RecID VARCHAR(255)
)
WHILE @CharPos > 0
BEGIN
SET @CharPos = CHARINDEX(@Delimiter, @Array)
IF @CharPos > 1
INSERT INTO @ArrayTable SELECT SUBSTRING(@Array, 1, @CharPos - 1)
ELSE
BREAK
SET @Array = SUBSTRING(@Array, @CharPos + 1, LEN(@Array))
END
SELECT * FROM @ArrayTable
GO
Given a delimiter (I used a pipe above, but your choice), it will turn the incoming list into row values, then store the results in a temporary table. After this piece is done, simply JOIN your temp table to the base table to get the values you need to manipulate.
MJM
October 8, 2009 at 10:33 am
Mark, it's time you learned about tally tables to replace while loops and save a few cpu cycles. 🙂
Some good articles
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://www.sqlservercentral.com/articles/T-SQL/67899/
An example for parsing:
declare @input varchar(100)
set @input = '|1|2|3|a|b|c|delta|bravo|#|@|'
;with tally (N) as
(select top 1000 row_number() over (order by (select null))
from master.sys.columns)
,Array AS
(select Row_Number() over (order by N) as N,
substring(@input,N+1,charindex('|',@input,N+1)-(N+1)) as element
from tally
where substring(@input,N,1) = '|'
and N < len(@input)
)
select * from Array
-- join your table that you want to check values
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 8, 2009 at 10:39 am
Bob Hovious 24601 (10/8/2009)
Mark, it's time you learned about tally tables for parsing and save a few cpu cycles. 🙂
Meh, I like to do it MY way though and I'll simply throw hardware at it eventually [/sarcasm]
Thanks for the reads! I will need to get caught up on some of these new-fangled techniques. That is why I come here - to broaden my horizons.
🙂
October 8, 2009 at 11:06 am
Thank you very much for your quick responses! I was able to get something working fairly quickly. I appreciate the help!!
Mike
October 8, 2009 at 12:19 pm
You're welcome, Mike.
Mark, the articles are very worthwhile and you will find tally tables an enormously useful (and FAST) technique. Take the time to do some comparisons with statistics for time and io on and you may be amazed at the percentage savings over a while loop.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply