mysql:找到行，该行的字段是"string"子字符串. [英] mysql: find rows which have a field that is a substring of "string"

问题描述

有一种方法可以编写一个sql查询，以查找所有字段值是给定字符串的子字符串的行.

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

示例:

table names

Name      |      Nickname
rohit            iamrohitbanga
banga            rohitnick
sinan            unur

查询应该类似于

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

怎么可能?

如果不可能的话，我将不得不在Java中实现该行为.我正在使用jdbc:mysql驱动程序来连接数据库.

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.

更新 您的解决方案有效

现在有点曲折. 如果我们要检查字段的子字符串是否作为我们指定的字符串的子字符串出现.

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

推荐答案

如果必须在文本使用中找到Name或Nickname之一，请使用

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.

这篇关于mysql:找到行，该行的字段是"string"子字符串.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！