如何编写一个SQL查询来查找para中的单词？

("select * from table where column like ?", ['%' + var1 + '%'])

shows results like "cabcd a", "xyabcd xyz", " xyz abc", "x abc","abc y". But i only want the output to be like "xyz abc", "x abc", "abc y". And abc is stored in var1

显示“cabcd a”,“xyabcd xyz”,“xyz abc”,“x abc”,“abc y”等结果。但我只希望输出像“xyz abc”,“x abc”,“abc y”。并且abc存储在var1中

7 个解决方案

#1

I would suggest:

我会建议:

where concat(' ', column, ' ') like "% abc %"

This will find the word anywhere in the column, assuming the only valid separator is a space.

这将在列中的任何位置找到单词,假设唯一有效的分隔符是空格。

Note: In SQLite, you would use || instead of concat(). Also, if you want performance, look into full text indexes.

注意:在SQLite中,您将使用||而不是concat()。此外,如果您想要性能,请查看全文索引。

EDIT:

The code if the value is stored in a variable looks like:

如果值存储在变量中的代码如下所示:

"select * from table where concat(' ', column, ' ') like ?", ['% ' + var1 + ' %'])

#2

SELECT *
FROM #temp1
WHERE name LIKE '% abc%'
OR name LIKE 'abc_%'

#3

Try this:

SELECT * FROM table_name 
WHERE column LIKE "% abc%" OR column LIKE "%abc %" OR column="abc";

#4

You can use REGEXP and the [[:<:]] and [[:>:]] word-boundary markers:

您可以使用REGEXP和[[:<:]]和[[:>:]]字边界标记:

SELECT * FROM table WHERE column REGEXP "[[:<:]]abc[[:>:]]";

#5

you have to declare the variable

你必须声明变量

DECLARE @Pattern_Val AS CHAR(7)
SET @Pattern_Val = '% abc %'

SELECT * FROM table WHERE column LIKE @Pattern_Val

CAUTION: Pay attention to the length of the CHAR - Definition!!

注意:注意CHAR的长度 - 定义!!

#6

Since your String is saved in a variable try something like this:

由于您的String保存在变量中,请尝试以下操作:

SELECT * FROM table WHERE (column LIKE CONCAT('% ', var1, '%') OR column LIKE CONCAT(var1,' %'));

Extra parentheses are to avoid confusion if you add more constraints (AND operators have precedence over OR).

如果添加更多约束(AND运算符优先于OR),额外的括号应避免混淆。

#7

How about searching with spaces on either side:

如何用两边的空格搜索:

select * from table where column like "% abc%" or  column like "%abc %"

#1