更新时间:2022-10-17 22:40:43
你有两个选择。更容易(在我看来)将它们包含在 [
和]
中。所以:
其中的模式像'[%]'
查找百分比字符。
第二个是使用不太可能在字符串中的转义字符,例如反驳:
其中的模式像''%'escape'`'
(请参阅语法这里。)
在这两种情况下,我建议您在应用程序层进行替换,但您也可以在SQL中,如果你真的想要:
其中的模式像替换(@pattern,'%','[%]')
而且,根据用户界面,给最终用户访问通配符可能是一件好事。
I have a table containing products. I need to make a query finding all the matching results to an user-input value. I am using SqlParameter
for the insertion of the inputs.
SqlCommand findProcutsByPattern = new SqlCommand(
"SELECT *" +
"FROM [Products]" +
"WHERE ProductName LIKE @pattern", connection);
findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + pattern + '%');
The problem comes when the user input string contains '_' or '%' as they're being interpreted as special characters. In the other hand, considering this:
Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code.
I shouldn't have such problems. Do I need to replace/escape all the '_' and '%' in the input string or is there a more elegant solution. I want the input to be considered as a literal.
I have a few records in the table which include special characters in the name(N_EW, N\EW, N%EW, N"EW, N'EW). Specifying \, ", and ' as the input works fine(considers them as literals).
You have two options. The easier (in my opinion) is to enclose them in [
and ]
. So:
where pattern like '[%]'
Looks for the percentage character.
The second is to use an escape character that is unlikely to be in the string, such as a backtick:
where pattern like '`%' escape '`'
(See the syntax here.)
In both cases, I would suggest that you make the substitution in the application layer, but you can also do it in SQL if you really want:
where pattern like replace(@pattern, '%', '[%]')
And, giving the end-user access to wildcards may be a good thing in terms of the user interface.