Finding dups within a delimited string column

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)

Finding dups within a delimited string column

Author

Topic

LaurieCox

158 Posts

Posted - 2012-05-17 : 13:10:49

SQL script for TestData table and functions at end of post.

I have actually solved this problem but it seems to run kind of slow (over a large set of data) and I wonder if there might be a better solution.

I have a column that has multiple dosages delimited by '- ' (dash + space). I need to identify if any of the dosages are duplicates within the column and then change the value of the event column depending on the result.

I found a split function hanging out in the database I am playing in. So I wrote a function that uses the split function and returns the count of the duplicate dosages. I then used it like this:


select Dosage_Combined
     , event
     , case 
           when dbo.ISC_duplicate_dosage_count(Dosage_Combined) > 1 and event = 'Reorder' then 'Change'
           else event
       end as event_modified
  from TestData

So is there a better way of doing this?

Thanks,

Laurie

SQL Script:


Test table and data:
create table TestData(
     event varchar (10),
     Dosage_Combined varchar(200))

Insert into TestData
SELECT 'Active',
       '- 2 mg, TAB, PO (1)ea QD - 2 mg, TAB, PO (1)ea QAM - 2 mg, TAB, PO (1)ea QPM - 2 mg, TAB, PO (1)ea QPM' 
        UNION ALL
SELECT 'Reorder', 
       '- 100 mg, TAB, PO (2)ea QAM - 100 mg, TAB, PO (1)ea QNoon - 100 mg, TAB, PO (1)ea QPM - 100 mg, TAB, PO (1)ea QPM'
        UNION ALL
SELECT 'Active', 
       '- 0.1 mg, TAB, PO (0.5)ea QHS - 0.1 mg, TAB, PO (0.5)ea PRN-QD' 
       UNION ALL
SELECT 'Reorder', '- 0.1 mg, TAB, PO (0.5)ea QHS - 0.1 mg, TAB, PO (0.5)ea PRN-QD' UNION ALL
SELECT 'Reorder', '- 125 mg, DRC, PO (2)ea QAM - 125 mg, DRC, PO (1)ea QNoon - 125 mg, DRC, PO (2)ea QHS' 
       UNION ALL
SELECT 'Active', '- 500 mg, ERT, PO (2)ea QPM' UNION ALL
SELECT 'Active', '- 25 mg, TAB, PO (1)ea QAM - 25 mg, TAB, PO (1)ea QPM - 25 mg, TAB, PO (1)ea PRN-QHS' UNION ALL
SELECT 'Change', '- 5 mg, TAB, PO (1)ea As Dir. - 5 mg, TAB, PO (1)ea As Dir.' UNION ALL
SELECT 'Discontinu', '- 5 mg, TAB, PO (1)ea As Dir. - 5 mg, TAB, PO (1)ea As Dir.'

My Function:
Create function [dbo].[ISC_duplicate_dosage_count]    
(                
 @Dosage_Combined VARCHAR(500)                
)  returns INT
as 
begin
	DECLARE @Dosages table(Dosage varchar(255))
	declare @Count int
	insert into @Dosages select * from fnSplit(@Dosage_Combined,'- ')
	set @Count = (select TOP 1 COUNT(*) FROM @Dosages GROUP BY Dosage ORDER BY COUNT(*) desc)
return @Count
end

Split function:
create FUNCTION [dbo].[fnSplit]
(
	@sInputList VARCHAR(8000) -- List of delimited items
	,@sDelimiter VARCHAR(2)-- delimiter that separates items

) RETURNS @List TABLE 

(item nVARCHAR(255))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

BEGIN

SELECT

@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,
                                  CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),
                                  LEN(@sInputList))))


IF LEN(@sItem) > 0

INSERT INTO @List SELECT @sItem

END

IF LEN(@sInputList) > 0

INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-17 : 16:18:55

Try this:


select Dosage_Combined
     , event
     , case 
           when DDC.duplicate IS NOT NULL and event = 'Reorder' then 'Change'
           else event
       end as event_modified
  from TestData
       OUTER APPLY
       (SELECT TOP(1) 1 AS duplicate
          FROM dbo.fnSplit(Dosage_Combined, '- ') AS T
         GROUP BY T.item
        HAVING COUNT(*) > 1) AS DDC

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

LaurieCox

158 Posts

Posted - 2012-05-18 : 08:39:47

wow ... thanks that is much faster than mine. I have to start thinking about the apply clause. This is the third time that it was the solution to a problem I posted here.

Thanks again.

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources