Needed a function to de-duplicate string lists, so I modified a similar UDF from SQLAuthority. That original UDF didn’t preserve the position of list elements or handle case sensitive comparisons, and I wasn’t too keen on the variable naming. Anyway, my version is below and let’s you specify the list delimiter as well as control case sensitivity, in addition to preserving the original sequence of list items.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE FUNCTION [dbo].[uf_listdedupe] ( @list varchar(8000), @delimiter char(1), @casesensitive bit ) RETURNS varchar(8000) /* Purpose: returns the @list with duplicate entries removed based on the character defined in @delimiter Author: Kevin J. Miller (www.websolete.com) Example: -- case insensitive de-dupe: SELECT myColumn, dbo.uf_listdedupe(myColumn,',',0) FROM myTable would return: one,two,three,ABC,One,def,abc,one one,two,three,ABC,def -- case sensitive de-dupe: SELECT myColumn, dbo.uf_listdedupe(myColumn,',',1) FROM myTable would return: one,two,three,ABC,One,def,abc,one one,two,three,ABC,One,def,abc */ AS BEGIN DECLARE @parsedlist TABLE ( seq int, item varchar(8000) ); DECLARE @listitem varchar(8000), @pos int, @distinctlist varchar(8000), @loop int; SET @loop = 0; SET @list = LTRIM(RTRIM(@list)) + @delimiter; SET @pos = CHARINDEX(@delimiter, @list, 1); WHILE @pos > 0 BEGIN SET @loop = @loop + 1; SET @listitem = LTRIM(RTRIM(LEFT(@list, @pos - 1))); IF @listitem <> '' BEGIN IF (@casesensitive = 0 AND NOT EXISTS (SELECT * FROM @parsedlist WHERE item = @listitem)) OR (@casesensitive = 1 AND NOT EXISTS (SELECT * FROM @parsedlist WHERE item = @listitem COLLATE Latin1_General_Bin)) BEGIN INSERT INTO @parsedlist (seq,item) VALUES (@loop,@listitem); END END SET @list = SUBSTRING(@list, @pos+1, LEN(@list)); SET @pos = CHARINDEX(@delimiter, @list, 1); END ;WITH ordereditems AS ( SELECT TOP 10000 item FROM @parsedList ORDER BY seq ) SELECT @distinctlist = COALESCE(@distinctlist + @delimiter,'') + item FROM ordereditems t RETURN @distinctlist END |