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

Answers


You were close

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

Alternative way (may be even faster)

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

If you are using MySQL you could

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

Need Your Help

File Upload PHP AJAX

php jquery ajax file-upload upload

I have an form which is being validated using the Jquery Validate plugin. This form has a file input using which the user can upload his/her profile pic. Now , using the Validate plugin submit hand...

Element-wise quotient of two columns in SQL

sql database

How can I combine the columns returned by two SELECT statements to give their element-wise quotient?