how can i write a sql query that finds rows where one column is a substring of another column

Question

I wish to find all rows in a table where one column is a substring of another column.

In other words, suppose I have a table (called people) with two columns: firstname and lastname, and I want to find all people like "rob robinowitz" and "jill bajillion".

Is there a way to do something like "select * from people where lastname like %firstname%"? (But something which actually works).

Answer 1

You were close

select * from people where lastname like '%' + firstname + '%'

Alternative way (may be even faster)

select * from people where charindex(firstname,lastname)>0

Answer 2

If you are using MySQL you could

SELECT * FROM people WHERE INSTR(lastname, firstname) <> 0