oracle like 模糊查询

LIKE 模糊查询

    字符匹配操作可以使用通配符 “%” 和 “_”：

    %：表示任意个字符，包括零个；

    _：表示一个任意字符；

 

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

 

"%" 和 "_"演示：

SQL> select * from dept where DNAME like '_A%';
 
DEPTNO DNAME          LOC
------ -------------- -------------
    30 SALES          CHICAGO

 

ESCAPE 演示：

SQL> insert into dept values(50,'BEIJING','JIANG%XI');
 
1 row inserted
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 BEIJING        JIANG%XI

 SQL> select * from dept where loc like '%\%%' escape '\';
 
DEPTNO DNAME          LOC
------ -------------- -------------
    50 BEIJING        JIANG%XI

 SQL> select * from dept where loc like '%e%%' escape 'e';
 
DEPTNO DNAME          LOC
------ -------------- -------------
    50 BEIJING        JIANG%XI