Oracle中的通配符

这是scott用户下的EMP表

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800		20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975		20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850		30
7782	CLARK	MANAGER	7839	1981/6/9	2450		10
7788	SCOTT	ANALYST	7566	1987/4/19	3000		20
7839	KING	PRESIDENT		1981/11/17	5000		10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7876	ADAMS	CLERK	7788	1987/5/23	1100		20
7900	JAMES	CLERK	7698	1981/12/3	950		30
7902	FORD	ANALYST	7566	1981/12/3	3000		20
7934	MILLER	CLERK	7782	1982/1/23	1300		10

"_"通配符（通配符代表一个字符）

--单个字符
select * from emp where JOB LIKE 'CL_RK' 

输出结果：

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800		20
7876	ADAMS	CLERK	7788	1987/5/23	1100		20
7900	JAMES	CLERK	7698	1981/12/3	950		30
7934	MILLER	CLERK	7782	1982/1/23	1300		10

 

"%"通配符（代表多个字符）

--多个字符
select * from emp where JOB LIKE 'MAN%' 

输出结果：

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7566	JONES	MANAGER	7839	1981/4/2	2975		20
7698	BLAKE	MANAGER	7839	1981/5/1	2850		30
7782	CLARK	MANAGER	7839	1981/6/9	2450		10

 

“[]”通配符（方括号范围内的单个字符）

下面使用了正则表达式“^”(匹配输入字符串输入的开始位置)

--查询出姓名以S或者A开头的记录
select * from emp where REGEXP_LIKE(ENAME,'^[SA]')

输出结果：

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800		20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7788	SCOTT	ANALYST	7566	1987/4/19	3000		20
7876	ADAMS	CLERK	7788	1987/5/23	1100		20

"[^]"通配符（不等于方括号内的字符）

例如：查找出姓名补位S,A开头的记录

select * from emp where REGEXP_LIKE(ENAME,'^[^SA]')

输出结果如下：

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975		20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850		30
7782	CLARK	MANAGER	7839	1981/6/9	2450		10
7839	KING	PRESIDENT		1981/11/17	5000		10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7900	JAMES	CLERK	7698	1981/12/3	950		30
7902	FORD	ANALYST	7566	1981/12/3	3000		20
7934	MILLER	CLERK	7782	1982/1/23	1300		10