mysql: find rows which have a field that is a subs

Question

is there a way to write an sql query that finds all rows where the field value is a substring of a given string.

Example:

table names

Name      |      Nickname
rohit            iamrohitbanga
banga            rohitnick
sinan            unur

query should be something like

select * from names where Name is a substring of "who is rohit";   // to get first row
select * from names where Nickname is a substring of "who is rohitnick";   // to get second row
select * from names where Name is a substring of "who is unur and banga"
or Nickname is substring of "who is unur and banga";   // to get second and third row

How is it possible?

If it is not possible then i'll have to achieve that behaviour in java. i am using jdbc:mysql driver to connect to the database.

Update your solutions work

now a bit of a twist. if we want to check if a substring of the field occurs as a substring of the string that we specify.

select * from names where Name is a substring of "who is sina";   // to get third row

Answer 1

One problem with all these approaches is your indexes go right out the window. You'll have to do a table scan for each and every row, which means your performance will only get worse and worse as the table size grows.

I'd rethink this approach if your table is going to be large. Maybe you need a indexed searcher like Lucene.

Answer 2

If one of Name or Nickname has to be found within the text use

SELECT *
FROM names
WHERE instr("who is Rohit", Name) > 0
   OR instr("who is Rohit", Nickname) > 0

No index can be used for that, so it might take long for large tables.

Answer 3

You can also reverse the LIKE condition.

select * from names where "who is rohit" LIKE  CONCAT('%', Name, '%');

Note, this probably isn't any faster than instr("who is Rohit", Name) but it may be.

Answer 4

SELECT * FROM names WHERE INSTR(Nickname,Name) > 0;

or, equivalently:

SELECT * FROM names WHERE LOCATE(Name,Nickname) > 0;