（转）sql通配符

背景：一次搞清sql查询中的通配符问题。

1 sql通配符

通配符主要以下几种： %、 _、 []、 [^] 。

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。SQL 通配符必须与 LIKE 运算符一起使用，必须放在引号内。

在 SQL 中，可使用以下通配符：

%：替代一个或多个字符。

_：仅替代一个字符。

[charlist]：字符列中的任何单一字符。

[^charlist]或者[!charlist]：不在字符列中的任何单一字符。 

1.1 举例分析

例子：

SELECT * FROM Persons WHERE City LIKE 'Ne%';

SELECT * FROM Persons WHERE LastName LIKE 'C_r_er';

选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：

SELECT * FROM Persons WHERE City LIKE '[ALN]%'
选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人:

SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

1. %：模糊匹配一个或多个字符。如，以ab开头

SELECT * FROM (  
    SELECT 'abc_2sd_38u' 'a'  
    UNION ALL  
    SELECT 'bcf' 'a'  
) c  
WHERE c.a LIKE 'ab%' 

a
-----------
abc_2sd_38u

 

2. _：占位符，匹配任意一个字符。如，以abc_开头

SELECT * FROM (  
    SELECT 'abc_2sd_38u' 'a'  
    UNION ALL  
    SELECT 'abcd_5d_3u' 'a'  
    UNION ALL  
    SELECT 'abc' 'a'  
) c  
WHERE c.a LIKE 'abc[_]%' 

a
-----------
abc_2sd_38u

 

3. []：目标匹配字符：匹配单个字符，若写了多个，匹配任一。如，所有以1或者2开头的

SELECT * FROM (  
    SELECT '1_21' 'a'  
    UNION ALL  
    SELECT '121' 'a'  
    UNION ALL  
    SELECT '131' 'a'  
    UNION ALL  
    SELECT '142' 'a'  
    UNION ALL  
    SELECT '223' 'a'  
    UNION ALL  
    SELECT '324' 'a'  
) x  
WHERE x.a LIKE '[12]%'  

a
-----------
1_21
121
131
142
223

 

4. [^]：目标匹配字符，[]相反。如，所有非1、2开头的

SELECT * FROM (  
    SELECT '1_21' 'a'  
    UNION ALL  
    SELECT '121' 'a'  
    UNION ALL  
    SELECT '131' 'a'  
    UNION ALL  
    SELECT '142' 'a'  
    UNION ALL  
    SELECT '223' 'a'  
    UNION ALL  
    SELECT '324' 'a'  
) x  
WHERE x.a LIKE '[^12]%'  

a
-----------
324

2 问题

2.1 模糊查询与通配符冲突

表结构描述：当前有一张表，此处假定node_info，存在一个字段node_path是由 ‘字母’、‘数字’ 及 ‘下划线_’组成，如： abc_1_de_f。

需求：查询所有node_path以‘ abc_’开头的记录

 

这个问题，自然而然的就想到，很简单，直接like % 模糊匹配就OK： 

SELECT * FROM mode_info WHERE node_path LIKE 'abc_%' 

 上述处理时错误的。要避免该错误，很简单：只要避免‘_’在此处作为通配符存在即可。如何避免呢，那就需要用到另外一个通配符'[]'。修改sql：

SELECT * FROM mode_info WHERE node_path LIKE 'abc[_]%'  

 

2.2 数据库中模糊查询不到结果

如下图，左右加 %% 是能够查询到结果的。

 但是要查询以Y04结尾的信息时候就查不到结果

 

后来仔细比对发现是数据库中字段后面存在两个空格，导致模糊结果无法匹配。

这种情况需要注意。