find if one column contains another column

2
votes

anyone know to how to create a query to find out if the data in one column contains (like function) of another column?

For example

ID||First_Name || Last_Name
------------------------
1 ||Matt       || Doe
------------------------
2 ||Smith      || John Doe
------------------------
3 ||John       || John Smith

find all rows where Last_name contains First_name. The answer is ID 3

thanks in advance

3
mysqlsqloracle
In Oracle, I'd try something like Select * from TABLE where instr(first_name, last_name) >= 1;. And MySql has an instr() function that behaves the same way. – Marc
this worked great, I would vote for this answer if I could, thanks – msjsam
I posted it as an answer. Wasn't certain I had it right, so thanks for confirming! – Marc

3 Answers

6
votes

Here's one way to do it:

Select *
  from TABLE
 where instr(first_name, last_name) >= 1;
0
votes

Try this:

select * from TABLE where last_name LIKE '%' + first_name + '%'
0
votes
WHERE Last_Name LIKE '%'+First_Name+'%'

You could also use INSTR(), but take note, both methods perform full-table scans, a general no-no when dealing with high-performance MySQL.