SQL函数（二）

来源：互联网发布：mac口红免税店价格编辑：程序博客网时间：2024/05/17 04:44

SQL> select 2312321.5435345 fromdual;

2312321.5435345

---------------

2312321.54

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) fromdual;

TO_CHAR(2312321

---------------

2312321.5435945

SQL> select to_char(2312321.5435945) + 1 from dual;

TO_CHAR(2312321.5435945)+1

--------------------------

2312322.54

SQL> select 2312321.5435945 + 1 from dual;

2312321.5435945+1

-----------------

2312322.54

SQL> select to_char(2312321.5435945) || 1 fromdual;

TO_CHAR(2312321.

----------------

2312321.54359451

SQL> select '1232' fromdual;

'123

----

1232

SQL> select '1232' + 1 from dual;

'1232'+1

----------

1233

SQL> select '1232' || 1 from dual;

'1232

-----

12321

SQL> select to_number('1232') || 1 fromdual;

TO_NU

-----

12321

SQL> select to_number('1232') + 1 fromdual;

TO_NUMBER('1232')+1

-------------------

1233

SQL> select * fromemployees

2 ;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY

----------- ---------- ---------- ------------------------------ ----------

1 James Smith CEO 800000

2 1 Ron Johnson sales manager 600000

3 2 Fred Hobbs Salesperson 150000

4 2 Susan Jones Salesperson 500000

SQL> select * fromcustomers

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers wherelast_name like 'Bl%'

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers wherelast_name like 'Bl%';

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'Bl');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'l');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers wherelast_name like '_l%';

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers wherelast_name like '%l%';

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'B..e')

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

5 Doreen Blue 20-5月 -70

SQL> select * fromcustomers

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'B[lmn][abcuvw][cde]')

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'B[lmn][abcuvw][cde]$')

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, '[A-Z][a-z]')

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, '[A-Za-z][a-z]')

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, '[A-Za-z0-9][a-z]');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * fromcustomers;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'l(ue|ack)');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, '[l](ue|ack)');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, '^[l](ue|ack)');

未选定行

SQL> select * from customers whereregexp_like(last_name, '^l(ue|ack)');

未选定行

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{4}')

2 ;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

4 Gail Black 800-555-1214

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{2}');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{2}$');

未选定行

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{3}$');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

5 Doreen Blue 20-5月 -70

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{4}$');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

4 Gail Black 800-555-1214

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{2}$');

未选定行

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{2}');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{2}$');

未选定行

SQL> select * From customers whereregexp_like(last_name, '^B[a-z]{2}$');

未选定行

SQL> select * From customers whereregexp_like(last_name, '^B[a-z]{2}');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * From customers whereregexp_like(last_name, '^[a-z]{2}');

未选定行

SQL> select * From customers whereregexp_like(last_name, '^B[a-z]{2}$');

未选定行

SQL> select * From customers whereregexp_like(last_name, '^B[a-z]{2, 5}$');

未选定行

SQL> select * From customers whereregexp_like(last_name, '^B[a-z]{2, 5}');

未选定行

SQL> select * From customers whereregexp_like(last_name, 'B[a-z]{2, 5}');

未选定行

SQL> select * fromcustomers;

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

2 Cynthia Green 05-2月 -68 800-555-1212

3 Steve White 16-3月 -71 800-555-1213

4 Gail Black 800-555-1214

5 Doreen Blue 20-5月 -70

SQL> select * from customers whereregexp_like(last_name, 'B[a-z]{4}');

CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE

----------- ---------- ------------------------ ------------

1 John Brown 01-1月 -65 800-555-1211

4 Gail Black 800-555-1214

SQL> select * from customers whereregexp_like(last_name, 'B[a-z]{4, 5}');

未选定行

SQL> select * from customers whereregexp_like(last_name, 'B[a-z]{2, 5}');

未选定行

SQL> select * from customers whereregexp_like(last_name, 'B[a-z]{2, 4}');

未选定行

SQL> select * from customers whereregexp_like(last_name, 'B[a-z]{4, }');

未选定行

SQL> select * from customers whereregexp_like(last_name, 'B[a-z]{2, }');

未选定行

SQL> select * from customers whereregexp_like(last_name, '[a-z]{2, }');

未选定行

SQL> exit

(9)如果查找要求是last_name中包含以’B’开头，紧接着的字符不是l，后续字符不限的字符串。可在[]中使

用^，中括号[]中的^ 表示否的含义：

【示例】SELECT * FROM customers WHEREREGEXP_LIKE(last_name,'B[^l]')

如果模式字符串是‘B[^lmn]’，则表示B后跟的字符不能是l,m,n三者之一。

(10)如果要求所查找的字符串中含有空白字符，可使用\s，\s符号匹配所有的空白字符，包括Tab字符。

【示例】SELECT * FROM customers WHEREREGEXP_LIKE(last_name,'[A-Za-z]+\s+[A-Za-z]+')

(11)对于[A-Za-z]，可使用[[:alpha:]]替换，效果相同。

查找last_name由四个字符组成的用户信息：

【示例】SELECT * FROM customers WHEREREGEXP_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 == 非字母数字下划线

0 0