Find Records Where One Column Contains Text of Another

Question

I have a table that contains a county name and another column for email address. I want to find all records where the county name is contained in the email address. I've tried LIKE and CHARINDEX but I have not been able to get this to work as expected yet - even using other examples I've found here on this site.

Sample Data:

UserID  email                            County
15854   test@test.com                    POLK
15018   test@adaircounty.org             ADAIR
15020   test@adaircounty.org             ADAIR
15022   test@adaircounty.org             ADAIR
15024   adamsrecorder@adamscountyia.com  ADAMS
15026   test@frontier.com                ADAMS
15028   test@co.allamakee.us             ALLAMAKEE
15030   lwelch@co.allamakee.ia.us        ALLAMAKEE
15032   test@co.allamakee.us             ALLAMAKEE
15034   test@appanoosecounty.net         APPANOOSE

Different variations I have tried include:

SELECT UserID , email , County
FROM   #CountyRecorders
--WHERE email LIKE '%' + County + '%';
--WHERE County LIKE '%' + email + '%';
--WHERE CHARINDEX(email,County) > 0;
--WHERE CHARINDEX(County,email) > 0;

How can I accomplish this desired result set? Thanks,

Answer 1

Both answers here will work - I have actually seen better performance using CHARINDEX in the WHERE clause for this type of thing but it's close. The way I would approach this, for performance reasons, is to use a computed column and index it. This way you only perform the calculation once. Using @Shnugo 's sample data (turned into a perm temp table for testing):

-- Table DDL and sample data
CREATE TABLE #tbl 
(
  UserID INT,
  email  VARCHAR(100),
  County VARCHAR(100),
  CountyInEmail AS CAST(SIGN(CHARINDEX(County,email)) AS BIT) PERSISTED
);
INSERT INTO #tbl VALUES
 (15854,'test@test.com                    ','POLK')
,(15018,'test@adaircounty.org             ','ADAIR')
,(15020,'test@adaircounty.org             ','ADAIR')
,(15022,'test@adaircounty.org             ','ADAIR')
,(15024,'adamsrecorder@adamscountyia.com  ','ADAMS')
,(15026,'test@frontier.com                ','ADAMS')
,(15028,'test@co.allamakee.us             ','ALLAMAKEE')
,(15030,'lwelch@co.allamakee.ia.us        ','ALLAMAKEE')
,(15032,'test@co.allamakee.us             ','ALLAMAKEE')
,(15034,'test@appanoosecounty.net         ','APPANOOSE');

-- Index on the "CountyInEmail" field
CREATE NONCLUSTERED INDEX nc_County ON #tbl(CountyInEmail)
INCLUDE (UserID, email, County)
;

-- Note the execution plan here (nonclustered index seek = good)
SELECT t.UserID, t.email, t.County, t.CountyInEmail
FROM   #tbl AS t
WHERE  t.CountyInEmail = 1;

Answer 2

You did not state the expected result, but - if I get this right - one of your approaches should return correctly. This works for me:

DECLARE @tbl TABLE(UserID INT,  email VARCHAR(100),County VARCHAR(100));
INSERT INTO @tbl VALUES
 (15854,'test@test.com                    ','POLK')
,(15018,'test@adaircounty.org             ','ADAIR')
,(15020,'test@adaircounty.org             ','ADAIR')
,(15022,'test@adaircounty.org             ','ADAIR')
,(15024,'adamsrecorder@adamscountyia.com  ','ADAMS')
,(15026,'test@frontier.com                ','ADAMS')
,(15028,'test@co.allamakee.us             ','ALLAMAKEE')
,(15030,'lwelch@co.allamakee.ia.us        ','ALLAMAKEE')
,(15032,'test@co.allamakee.us             ','ALLAMAKEE')
,(15034,'test@appanoosecounty.net         ','APPANOOSE');

SELECT *
FROM @tbl 
WHERE eMail LIKE '%' + County + '%';

All rows are returned - except the "test" and the "frontier.com" row.

What might disturb: If your columns are under a case sensitive collation the capitalized ADAIR is not the same as adair. You can try either to use LOWER on both sides or to apply a insensitive collation with COLLATE

Answer 3

You should just be able to use CHARINDEX for this Do

Select UserId, email, County
FROM #CountyRecorders
WHERE CHARINDEX(County,email) > 0

your casing matters here though