用于 LIKE 运算符通配符搜索的 T-SQL 特殊字符转义 [英] T-SQL special characters to escape for LIKE operator wildcard search

问题描述

SQL Server 具有 LIKE 运算符来处理通配符搜索.我的客户想要在应用程序的用户界面中使用*"(星号)字符作为通配符.我只是想知道在执行 LIKE 通配符搜索之前，除了%"(百分比)字符本身之外，是否还有我需要担心的标准字符(在 SQL Server 中用作特殊字符)，以防他们的关键字包含"%" 并且需要在实际字符串中找到一个 "%".如果有，它们是什么?

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?

所以请假设 [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', ']', '[]]')  -- ???

示例:

SET @p = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@p, ']', '[]]'), '[', '[[]'), '_', '[_]'), '%', '[%]'), '*', '%')

SELECT 'xxxxxxxxx%xxxxxx' LIKE @p

SELECT [table1].[column1] LIKE @p

推荐答案

虽然我认为转义 ']' 是不必要的，但您似乎已经掌握了所有内容.从技术上讲，您应该只需要转义左括号 ('[').

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;

这篇关于用于 LIKE 运算符通配符搜索的 T-SQL 特殊字符转义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！