April 14, 2010 at 7:25 am
I've run into a situation where a drug data source provides two fields that contain delimited values such that the delimited values in field 1 are in sync with the delimited values in field 2.
Field 1 contains delimited drug class codes & field 2 contains the delimited drug class code descriptions. The reason for this approach is that a single prescribed drug could be traced to more than one drug class.
Here's an example typical of the source data:
Drug DrugClassCode DrugClassName
=========== ============= ====================================================
milnacipran |123|343| |Antidepressant (SNRI)|Fibromyaglia - Antidep(SNRI)|
I'd like to achieve an output that looks as follows:
Drug DrugClassCode DrugClassName
=========== ============= =====================================
milnacipran 123 Antidepressant (SNRI)
milnacipran 343 Fibromyaglia - Antidep(SNRI)
I'm used to parsing a single delimited field using the tally table technique, but I haven't quite found an elegant solution for my current dual-field delimited situation.
Would the recommended approach be to parse each field separately into two distinct tables, and then join them by Drug to get the desired output?
Thanks in advance,
Pete
April 14, 2010 at 9:23 am
I don't know what would Jeff do and it probably would be better than whatever I my suggest...
That said, here's my two cents:
-- Hope Jeff doesn't mind I borrowed his code :-)
CREATE FUNCTION [dbo].[fSplit]
(
@Parameter VARCHAR(8000),
@SplitOn char(1)
)
RETURNS @Elements TABLE
(
ID INT identity(1,1),
Value VARCHAR(8000)
)
AS
BEGIN
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = @splitOn + @Parameter + @SplitOn
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements (Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(@splitOn,@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = @splitOn --Notice how we find the comma
RETURN
END
DECLARE @test-2 TABLE (
Drug varchar(50),
DrugClassCode varchar(50),
DrugClassName varchar(500)
)
INSERT INTO @test-2 VALUES('milnacipran', '|123|343|', '|Antidepressant (SNRI)|Fibromyaglia - Antidep(SNRI)|')
SELECT A.Drug, B.Value AS DrugClassCode, C.Value AS DrugClassName
FROM @test-2 AS A
CROSS APPLY dbo.fSplit(DrugClassCode, '|') AS B
CROSS APPLY dbo.fSplit(DrugClassName, '|') AS C
WHERE B.Value <> ''
AND B.Value <> ''
AND B.ID = C.ID
Hope this helps
Gianluca
-- Gianluca Sartori
April 14, 2010 at 9:44 am
Gianluca, this is great.
I was just starting to draft a script using Cross Apply when I noticed I'd received a response to my post in my email. You beat me to it...
Your script definitely does the trick. Should prove useful to anyone else who has run into a similar problem.
Thanks,
--Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply