Search if there is a string word between two different tables in a comma delimited field

Question

I have two tables:

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

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

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

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

thanks

Answer 1

First, separate the comma-delimited field into a temporary table or table variable. That way, you can exactly or precisely join the strings. Create a row for each name and include a key column of a specific type that helps you adjust the rows.

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

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: [e-mail] 

result:

 andy john mark 

After you have two temporary tables, do a FULL OUTER JOIN and include the “found in both” column with the given value. You will get a NULL value for names not found in both - and you can consider NULL as the value "False".

Can you indicate why you need to get a boolean for matches between two tables? What are you going to do next? Sometimes an explanation of this will lead to better solutions. You may find that you make haste assumptions. Best, Bill.

Answer 2

Unverified:

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

should return some value > 0 if the name is found in one of the lists of the first table. Do the same for the second table and return true if both SELECT returned a nonzero value.

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

Answer 3

Here is a script that creates two test tables and returns a list of names with "True" if the name is specified in both tables. It works using the left join to find names that are in both tables or only in table A. This result set is combined into the right join to get 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 4

This is part 2 of me above, so I can add additional code. This part explains how to get the boolean value of whether there is a match between the tables after you break 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 that takes a row separated by commas and returns a table (single column) of the row.

 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. 

It will also make your code available. But remember, this can be a performance bottleneck if you use multiple lines. It runs for each line.

UPDATED !!!

Yes, you can use the join from another answer after receiving the tables.

Answer 6

try it

 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' 

- launch the program

 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 

Conclusion:

EmployeeTypeA EmployeeTypeB

 sam sam