Compare Columns Where One is Similar to Part of Another

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.


Reverse the where, to something like this:

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

Switch the arguments to LIKE in the WHERE clause:

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

The wildcard must be the second argument.


This worked for me:

SELECT *
FROM `table`
WHERE `col1` NOT LIKE CONCAT('%', `col2`, '%')

Found it here: http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/

Somehow it only worked correctly with the concat-function (?).

Tags:

Sql

Sql Server