Mysql: find strings that have a field that is a substring of "string"

Question

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

Example:

table names

Name      |      Nickname
rohit            iamrohitbanga
banga            rohitnick
sinan            unur

the request 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 this possible?

If this is not possible, I will need to execute this behavior in java. I am using the jdbc: mysql driver to connect to the database.

Update your solutions work

now swirls a little. if we want to check if a substring of a field exists as a substring of the specified string.

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

Answer 1

Name Nickname ,

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

, .

Answer 2

, . , , .

, . , , Lucene.

Answer 3

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

, :

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

Answer 4

LIKE.

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

, , , , instr ( " ", "" ), .