0 Comments
  •   Posted in: 
  • SQL

I recently had a friend ask for help comparing two comma seperated lists in SQL server using a UDF. I started based around the Split UDF I wrote, and continued from there. The simplest and most performant thing I could think of was to use an INNER JOIN to compare the table values of the two lists. Then just compare the counts you get back to make sure they're all the same. Here's the UDF:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO

CREATEFUNCTIONIsCommaListMatch
(
@list1varchar(255),
@list2varchar(255)
)
RETURNSbit
AS
BEGIN
DECLARE@isMatchbit

DECLARE@list1ValuesTABLE(valint)
DECLARE@list2ValuesTABLE(valint)

INSERTINTO@list1ValuesSELECT*FROMdbo.Split(@list1)
INSERTINTO@list2ValuesSELECT*FROMdbo.Split(@list2)

DECLARE@count1int
DECLARE@count2int
DECLARE@count1Joinint
DECLARE@count2Joinint

SELECT@count1=COUNT(val)FROM@list1Values
SELECT@count2=COUNT(val)FROM@list2Values

SELECT
@count1Join=COUNT(list1Values.val),
@count2Join=COUNT(list2Values.val)
FROM
@list1Values list1Values
INNERJOIN@list2Values list2Values
ON
list1Values.val=list2Values.val

IF@count1=@count2AND@count1=@count1JoinAND@count2=@count2Join
SET@isMatch=1
ELSE
SET@isMatch=0

RETURN@isMatch
END

GO

As always, feel free to contact me if you have ideas on improving this.