T-SQL和WHERE LIKE %Parameter%子句

I was trying to write a statement which uses the WHERE LIKE '%text%' clause, but I am not receiving results when I try to use a parameter for the text. For example, this works:

我试图编写一个语句，该语句使用“%text%”子句，但是当我尝试为文本使用一个参数时，没有收到结果。例如,这个工作原理:

SELECT Employee WHERE LastName LIKE '%ning%'

This would return users Flenning, Manning, Ningle, etc. But this statement would not:

这将返回Flenning、Manning、Ningle等用户，但该声明不会:

DECLARE @LastName varchar(max)
SET @LastName = 'ning'
SELECT Employee WHERE LastName LIKE '%@LastName%'

No results found. Any suggestions? Thanks in advance.

没有结果。有什么建议吗?提前谢谢。

3 个解决方案

#1

100

It should be:

应该是:

...
WHERE LastName LIKE '%' + @LastName + '%';

Instead of:

而不是:

...
WHERE LastName LIKE '%@LastName%'

Or this:

或:

...
WHERE LastName LIKE Concat('%',@LastName,'%')

#2

The correct answer is, that, because the '%'-sign is part of your search expression, it should be part of your VALUE, so whereever you SET @LastName (be it from a programming language or from TSQL) you should set it to '%' + [userinput] + '%'

正确的答案是，因为'%'-符号是搜索表达式的一部分，所以它应该是值的一部分，所以无论您在哪里设置@LastName(无论是来自编程语言还是来自TSQL)，您都应该将其设置为'%' + [userinput] + '%'

or, in your example:

或者,在你的例子:

DECLARE @LastName varchar(max)
SET @LastName = 'ning'
SELECT Employee WHERE LastName LIKE '%' + @LastName + '%'

#3

you may try this one, used CONCAT

你可以试试这个，二手的

WHERE LastName LIKE Concat('%',@LastName,'%')

#1

100