CODEWITHSUNDEEP
sqlsql-server
craigmj
craigmj

Reputation: 788

Compare Columns Where One is Similar to Part of Another

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?

Upvotes: 28

Views: 110036

Answers (8)

Quentin
Quentin

Reputation: 701

With Google BigQuery:

Fullname NOT LIKE ('%' || FirstName || '%')

Upvotes: 0

p.campbell
p.campbell

Reputation: 100587

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.

Upvotes: 12

Jon
Jon

Reputation: 16728

It looks OK, except you probably want to switch the order around in your where:

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

Upvotes: 1

Nwaokoro jacinta chinyere
Nwaokoro jacinta chinyere

Reputation: 21

Oracle expects number when + is used. For string, please use the samle below :

SELECT ID, FullName, FirstName
FROM tblNames
WHERE FullName like '%' || FirstName||'%'

To compare one column of a table to a column of another table, please do the following 

select a.*,table_2_col_1, table_2_col_2 from (select table_1_col_1, table_1_col_2 from table_1 where
)  a, table_2 where   table_1_col_1 like '%' || table_2_col_1 ||'%'

Upvotes: 2

Zlosk
Zlosk

Reputation: 1

Parentheses also would have fixed the issue.

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

Upvotes: 0

Jeroen
Jeroen

Reputation: 141

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 (?).

Upvotes: 14

jzd
jzd

Reputation: 23629

Reverse the where, to something like this:

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

Upvotes: 40

WReach
WReach

Reputation: 18271

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.

Upvotes: 3

Related Questions