CODEWITHSUNDEEP
sqlt-sql
Hugh
Hugh

Reputation: 2569

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

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

Upvotes: 2

Views: 1000

Answers (2)

Dmitry Khalatov
Dmitry Khalatov

Reputation: 4369

You were close

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

Alternative way (may be even faster)

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

Upvotes: 5

kmkaplan
kmkaplan

Reputation: 31

If you are using MySQL you could

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

Upvotes: 0

Related Questions