Search if a string word exists between two different tables in a comma-delimited field

Question

I have two tables:

EmployeeTypeA table
Name varchar(2000) field contains - 'john,sam,doug'

EmployeeTypeB table
Name varchar(2000) field contains - 'eric,sam,allen,stephanie'

What is the most efficient way to return a true or false when a name is found in both lists using MS SQL? This needs to be done within a stored procedure so I cannot use a programming language to manipulate it outside of SQL. In this example since 'sam' is in both tables I would want to return a true (or 0,etc)

Should I separate the comma-delimited field first and then put the items in a temp table? Or use cursors?

Thanks

Answer 1

Untested:

SELECT COUNT(*) FROM EmployeeTypeA 
 WHERE ',' + nameListField + ',' LIKE '%,' + @searchedName + ',%'

should return some value > 0 if the name has been found in one of the lists of the first table. Do the same for the second table and return true if both SELECTs returned a non-zero value.

PS: If you have the authority to change the database design: Do it. A normalized database should not contain comma-separated lists but rather subtables with a foreign key relationship.

Answer 2

Here is a script that creates the two test tables and returns a list of the names with 'True' if the name is in both tables. It works by using a left join to find the names that are in both tables or only in table A. This result set is unioned to a right join to get the names that are only in table B.

DROP TABLE EmployeeTypeA
DROP TABLE EmployeeTypeB
GO

CREATE TABLE EmployeeTypeA
    (Name VARCHAR(2000))
GO

CREATE TABLE EmployeeTypeB
    (Name VARCHAR(2000))
GO

INSERT INTO EmployeeTypeA VALUES ('john')
INSERT INTO EmployeeTypeA VALUES ('sam')
INSERT INTO EmployeeTypeA VALUES ('doug')

INSERT INTO EmployeeTypeB VALUES ('eric')
INSERT INTO EmployeeTypeB VALUES ('sam')
INSERT INTO EmployeeTypeB VALUES ('allen')
INSERT INTO EmployeeTypeB VALUES ('stephanie')
GO

SELECT
    eta.Name,
    CASE
     WHEN etb.Name IS NULL THEN 'False'
     ELSE 'True'
    END
FROM
    EmployeeTypeA eta
    LEFT JOIN EmployeeTypeB etb ON
     eta.Name = etb.Name

UNION

SELECT
    etb.Name,
    'False'
FROM
    EmployeeTypeA eta
    RIGHT JOIN EmployeeTypeB etb ON
     eta.Name = etb.Name
WHERE
    eta.Name IS NULL

GO

Answer 3

Separate the comma-delimited field first into a temporary table or table variable. That way, you can join or match rows accurately. Make a row for each name and include a key column of some sort that will help you correlate the rows.

The best way to do this is with a "helper table" like so:

DECLARE @numbers TABLE (number int)
DECLARE @i int 
SET @i = 1
WHILE (@i < 1001)
BEGIN    
INSERT INTO @numbers (number) VALUES (@i)    
SET @i = @i+1
END


DECLARE @TestString VARCHAR(200)
SET @TestString = 'andy,john,mark'
DECLARE @RowDelimiter VARCHAR(1)
SET @RowDelimiter=','

SELECT SUBSTRING(@TestString+@RowDelimiter, number, 
    CHARINDEX(@RowDelimiter, @TestString+@RowDelimiter, number) - number) 
FROM @numbers 
WHERE number <= LEN(@TestString) 
AND SUBSTRING(@RowDelimiter+ @TestString, number, 1) = @RowDelimiter
ORDER BY number 
-- helper table technique: [email protected]

the result is:

andy
john
mark

Once you have the two temporary tables, then do a FULL OUTER JOIN and include your "found in both" column with a set value. You'll get the NULL value for names not found in both - and you can treat the NULL as the "False" value.

Can you mention why you need to get a boolean value for matches between the two tables? What are you going to do with it next? Sometimes explaining that will lead to better solutions. You might find that you are making assumptions hastily. Best, Bill.

Answer 4

This is part 2 from me above so I can add additional code. This part explains how to get your boolean value of whether or not there is a match between the tables after you've ripped your names into separate rows.

DECLARE @LeftTable TABLE (thisid int, thisname varchar(50))

INSERT INTO @LeftTable VALUES  (1, 'andy')
INSERT INTO @LeftTable VALUES  (2, 'bill')
INSERT INTO @LeftTable VALUES  (3, 'zed')

DECLARE @RightTable TABLE (thisid int, thisname varchar(50))

INSERT INTO @RightTable VALUES  (1, 'chris')
INSERT INTO @RightTable VALUES  (2, 'bill')
INSERT INTO @RightTable VALUES  (3, 'zed')

SELECT 
a.thisname AS theleftname, 
b.thisname AS therightname, 
CASE
    WHEN (ISNULL(a.thisname,'') = '' OR ISNULL(b.thisname,'') = '') THEN 'False'
    ELSE 'True'
END     
AS namematches
FROM @LeftTable a
FULL OUTER JOIN @RightTable b
ON a.thisname = b.thisname
-- www.caliberwebgroup.com

Here are the results:

theleftname therightname namematches 
NULL    chris False
bill    bill True
zed  zed  True
andy    NULL False

Answer 5

You can write a table valued function which takes in a comma separated string and returns a table (one column) of string.

Create FUNCTION [dbo].[SplitStrings]
(
    @StringList varchar(max)
)
RETURNS 
@Outputable table
(
    ParsedItem varchar(2000)
)

as

-- you can have a while loop here to populate the table.

This will also make your code resuable. But remember this might a performance bottleneck if you use for a lot of rows..It runs for every row.

UPdated!!!

Yes offcourse you can use the join from other answer once you get the tables.

Answer 6

Try this

declare @EmployeeTypeA table(Name VARCHAR(2000))
insert into @EmployeeTypeA select 'john,sam,doug'
declare @EmployeeTypeB table(Name VARCHAR(2000))
insert into @EmployeeTypeB select  'eric,sam,allen,stephanie'

--Program starts

declare @xA xml
declare @xB xml
select @xA = '<i>' + REPLACE(Name, ',', '</i><i>') + '</i>'  from @EmployeeTypeA
select @xB = '<i>' + REPLACE(Name, ',', '</i><i>') + '</i>'  from @EmployeeTypeB

select 
 EmployeeTypeA
 ,EmployeeTypeB     

from (
SELECT 
  EmployeeTypeA
  ,i.value('.', 'VARCHAR(MAX)') EmployeeTypeB
  FROM @xB.nodes('//i') x(i)
  cross apply(
  SELECT i.value('.', 'VARCHAR(MAX)') EmployeeTypeA
  FROM @xA.nodes('//i') x(i)) Y) Res(EmployeeTypeA,EmployeeTypeB)
  where EmployeeTypeA = EmployeeTypeB

Output:

EmployeeTypeA EmployeeTypeB

sam             sam