SQL> select 2312321.5435345 from dual;
2312321.5435345
---------------
SQL> select to_char(2312321.5435345, 'S0999999.9999') from dual;
TO_CHAR(23123
-------------
+2312321.5435
SQL> select to_char(2312321.5435345, 'S0000999999.9999') from dual;
TO_CHAR(2312321.
----------------
+0002312321.5435
SQL> select to_char(2312321.5435945, 'S0000999999.9999') from dual;
TO_CHAR(2312321.
----------------
+0002312321.5436
SQL> select to_char(2312321.5435945) from dual;
TO_CHAR(2312321
---------------
2312321.5435945
SQL> select to_char(2312321.5435945) + 1 from dual;
TO_CHAR(2312321.5435945)+1
--------------------------
SQL> select 2312321.5435945 + 1 from dual;
2312321.5435945+1
-----------------
SQL> select to_char(2312321.5435945) || 1 from dual;
TO_CHAR(2312321.
----------------
2312321.54359451
SQL> select '1232' from dual;
'123
----
1232
SQL> select '1232' + 1 from dual;
----------
SQL> select '1232' || 1 from dual;
'1232
-----
12321
SQL> select to_number('1232') || 1 from dual;
TO_NU
-----
12321
SQL> select to_number('1232') + 1 from dual;
TO_NUMBER('1232')+1
-------------------
SQL> select * from employees
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
SQL> select * from customers
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where last_name like 'Bl%'
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where last_name like 'Bl%';
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'Bl');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'l');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where last_name like '_l%';
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where last_name like '%l%';
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'B..e')
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'B[lmn][abcuvw][cde]')
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'B[lmn][abcuvw][cde]$')
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, '[A-Z][a-z]')
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, '[A-Za-z][a-z]')
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, '[A-Za-z0-9][a-z]');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'l(ue|ack)');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, '[l](ue|ack)');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, '^[l](ue|ack)');
未选定行
SQL> select * from customers where regexp_like(last_name, '^l(ue|ack)');
未选定行
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{4}')
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{2}');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{2}$');
未选定行
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{3}$');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{4}$');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{2}$');
未选定行
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{2}');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{2}$');
未选定行
SQL> select * From customers where regexp_like(last_name, '^B[a-z]{2}$');
未选定行
SQL> select * From customers where regexp_like(last_name, '^B[a-z]{2}');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * From customers where regexp_like(last_name, '^[a-z]{2}');
未选定行
SQL> select * From customers where regexp_like(last_name, '^B[a-z]{2}$');
未选定行
SQL> select * From customers where regexp_like(last_name, '^B[a-z]{2, 5}$');
未选定行
SQL> select * From customers where regexp_like(last_name, '^B[a-z]{2, 5}');
未选定行
SQL> select * From customers where regexp_like(last_name, 'B[a-z]{2, 5}');
未选定行
SQL> select * from customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'B[a-z]{4}');
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where regexp_like(last_name, 'B[a-z]{4, 5}');
未选定行
SQL> select * from customers where regexp_like(last_name, 'B[a-z]{2, 5}');
未选定行
SQL> select * from customers where regexp_like(last_name, 'B[a-z]{2, 4}');
未选定行
SQL> select * from customers where regexp_like(last_name, 'B[a-z]{4, }');
未选定行
SQL> select * from customers where regexp_like(last_name, 'B[a-z]{2, }');
未选定行
SQL> select * from customers where regexp_like(last_name, '[a-z]{2, }');
未选定行
SQL> exit
(9)如果查找要求是last_name中包含以’B’开头,紧接着的字符不是l,后续字符不限的字符串。可在[]中使
用^,中括号[]中的^ 表示否的含义:
【示例】SELECT * FROM customers WHERE REGEXP_LIKE(last_name,'B[^l]')
如果模式字符串是‘B[^lmn]’,则表示B后跟的字符不能是l,m,n三者之一。
(10) 如果要求所查找的字符串中含有空白字符,可使用\s,\s符号匹配所有的空白字符,包括Tab字符。
【示例】SELECT * FROM customers WHERE REGEXP_LIKE(last_name,'[A-Za-z]+\s+[A-Za-z]+')
(11) 对于[A-Za-z],可使用[[:alpha:]]替换,效果相同。
查找last_name由四个字符组成的用户信息:
【示例】SELECT * FROM customers WHERE REGEXP_LIKE(last_name,'^[[:alpha:]]{4}$')
其它的同义符如下:
[[:digit:]] == [0-9]
[[:alphanum:]] == [0-9a-zA-Z]
[[:lower:]] == [a-z]
[[:upper:]] == [A-Z]
[[:blank:]] == \s
\d == [0-9]
\D == [^0-9]
\w == 字母数字下划线
\W == 非字母数字下划线