Compare Columns Where One is Similar to Part of Another

Question

I'm trying to write a Select statement where I can see if one column is like part of another.

tblNames  ID    FullName                   FirstName 1     Mr. John Doe, CEO          John 2     Mr. Jake Doe, Exec        Jake 3     Mrs. Betty Smith, Chair     Jill 

The query should return:

3 | Mrs.Betty Smith, Chair | Jill 

However mine just returns every row in the table:

SELECT ID, FullName, FirstName FROM tblNames WHERE '%' + FirstName + '%' not like Fullname 

Any ideas?

Answer 1

Reverse the where, to something like this:

Fullname not like '%' + FirstName + '%'  

Answer 2

Try this:

SELECT * FROM tblNames WHERE  ISNULL( CHARINDEX (FirstName , FullName),0) = 0 

The CHARINDEX will be faster (more performant) than a LIKE clause, as it doesn't have to take wildcards into account. The sample data above with small numbers of rows won't show a performance benefit, but when in the millions of rows, CHARINDEX would perform better.