SQL Server has the LIKE operator to handle wildcard searches. My customer wants to use the "*" (asterisk) character in the user interface of an application as the wildcard character. I'm just wondering if there are any standard characters that I need to worry about (that are used as special characters in SQL Server) besides the "%" (percent) character itself before performing a LIKE wilcard search in case their keyword contains a "%" and needs to find a "%" in the actual string. If so, what are they?
SQL Server具有LIKE运算符来处理通配符搜索。我的客户希望在应用程序的用户界面中使用“*”(星号)字符作为通配符。我只是想知道是否有任何标准字符我需要担心(在SQL Server中用作特殊字符)除了“%”(百分比)字符本身之前执行LIKE威尔卡搜索以防其关键字包含“%”并且需要在实际字符串中找到“%”。如果是这样,他们是什么?
So please assume that [table1].[column1] will never have a "*" (asterisk) in the text string!
所以请假设[table1]。[column1]在文本字符串中永远不会有“*”(星号)!
Here's what I have so far. Do I need to handle more situations other than the standard "%"
character and the custom "*"
这是我到目前为止所拥有的。我是否需要处理除标准“%”字符和自定义“*”之外的更多情况
-- custom replacement
select REPLACE('xxx*xxx', '*', '%')
-- standard replacements
select REPLACE('xxx%xxx', '%', '[%]')
select REPLACE('xxx_xxx', '_', '[_]') -- ???
select REPLACE('xxx[xxx', '[', '[[]') -- ???
select REPLACE('xxx]xxx', ']', '[]]') -- ???
Example:
例:
SET @p = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@p, ']', '[]]'), '[', '[[]'), '_', '[_]'), '%', '[%]'), '*', '%')
SELECT 'xxxxxxxxx%xxxxxx' LIKE @p
SELECT [table1].[column1] LIKE @p
2 个解决方案
#1
10
It looks like you got them all, although I think escaping ']' is unnecessary. Technically you should just need to escape the opening bracket ('[').
看起来你得到了所有这些,虽然我认为逃避']'是不必要的。从技术上讲,你应该只需要逃离开口括号('[')。
DECLARE @Table1 TABLE
(
Column1 VARCHAR(32) NOT NULL PRIMARY KEY
);
INSERT @Table1(Column1)
VALUES
('abc%def'),
('abc_def'),
('abc[d]ef'),
('abc def'),
('abcdef');
DECLARE @p VARCHAR(32) = 'abc*]*';
DECLARE @Escaped VARCHAR(64) = REPLACE(@p, '[', '[[]');
SET @Escaped = REPLACE(@Escaped, '_', '[_]');
SET @Escaped = REPLACE(@Escaped, '%', '[%]');
SET @Escaped = REPLACE(@Escaped, '*', '%');
SELECT T.Column1
FROM @Table1 T
WHERE T.Column1 LIKE @Escaped;
#2
2
Looks good!! - Have a look here for all the information related to the LIKE
clause.
看起来不错!! - 看看这里有关LIKE子句的所有信息。
Also List of special characters for SQL LIKE clause
还有SQL LIKE子句的特殊字符列表
#1
10
It looks like you got them all, although I think escaping ']' is unnecessary. Technically you should just need to escape the opening bracket ('[').
看起来你得到了所有这些,虽然我认为逃避']'是不必要的。从技术上讲,你应该只需要逃离开口括号('[')。
DECLARE @Table1 TABLE
(
Column1 VARCHAR(32) NOT NULL PRIMARY KEY
);
INSERT @Table1(Column1)
VALUES
('abc%def'),
('abc_def'),
('abc[d]ef'),
('abc def'),
('abcdef');
DECLARE @p VARCHAR(32) = 'abc*]*';
DECLARE @Escaped VARCHAR(64) = REPLACE(@p, '[', '[[]');
SET @Escaped = REPLACE(@Escaped, '_', '[_]');
SET @Escaped = REPLACE(@Escaped, '%', '[%]');
SET @Escaped = REPLACE(@Escaped, '*', '%');
SELECT T.Column1
FROM @Table1 T
WHERE T.Column1 LIKE @Escaped;
#2
2
Looks good!! - Have a look here for all the information related to the LIKE
clause.
看起来不错!! - 看看这里有关LIKE子句的所有信息。
Also List of special characters for SQL LIKE clause
还有SQL LIKE子句的特殊字符列表