Check to see if one field is contained within another field in a different table

If you have two different tables where there are two fields that you want to check to find out if one of those fields is contained inside another field while both fields belong to separate tables in the same database. You can use CHARINDEX function to do that.

For example lets take zip code. Table2 has a single zip code, and Table1 has a field with comma separated list of zip codes, i.e. 08036, 08032, 07338, 07339, and you need to check whether single zip code from Table2 matches one of those comma separated zip codes.

SELECT DISTINCT t1.Name, t2.Name, t2.ZipCode, t1.ZipCodes
FROM Table1 as t1 
INNER JOIN Table2 as t2 ON CHARINDEX(t2.ZipCode , t1.ZipCodes ) > 0 

The CHARINDEX function returns the starting position of a character, or a string of characters within another character string. So the above SELECT checks if there are any records where CHARINDEX is greater than 0, meaning single zip code in that row was found among comma separated zip codes.

There is another function called PATINDEX which works similar to CHARINDEX except it supports wildcard charaters.

Here is an example:

SELECT DISTINCT t1.Name, t2.Name, t2.ZipCode, t1.ZipCodes
FROM Table1 as t1 
INNER JOIN Table2 as t2 ON PATINDEX('%33%' , t1.ZipCodes ) > 0 

or

PATINDEX('%BC%', 'ABCD')

or

PATINDEX('AB%', 'ABCD')