oracle常用函数实例演示

字符串函数：

    1.返回字符变量

     1.1 concat

SQL> select concat('ABC',' c') from dual;

CONCA
-----
ABC c

SQL> select concat(first_name,last_name) name from employees where employee_id=152;

NAME
---------------------------------------------
PeterHall

SQL> SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job"
FROM employees
WHERE employee_id = 152;

Job
------------------------------------------------------
Hall's job category is SA_REP

   ||实现，以上三个例子也可以如下实现

SQL> select 'ABC'||' c' from dual;

'ABC'
-----
ABC c

SQL> select first_name||last_name from employees where employee_id=152;

FIRST_NAME||LAST_NAME
---------------------------------------------
PeterHall

SQL> select last_name||'''s job category is '||job_id from employees where employee_id=152;

LAST_NAME||'''SJOBCATEGORYIS'||JOB_ID
------------------------------------------------------
Hall's job category is SA_REP

1.2

INITCAP首字母大写显示

SQL> select initcap('abc bbb') from dual;

INITCAP
-------
Abc Bbb

1.3 NLS_INITCAP()该函数也是首字母大写，不过是可以带有一个参数的。

SQL> select nls_initcap('ABCD efgh') from dual;

NLS_INITC
---------
Abcd Efgh

1.4LOWER()将所有字母转换为小写显示。并且也有个继承函数NLS_LOWER().

    UPPER()将所有字母转换为大写显示，也有个NLS_UPPER().

SQL> select lower('APP dee fQQ Gbb') lower from dual;

LOWER
---------------
app dee fqq gbb

SQL> select upper('aBBcc eee PPdd') from dual;

UPPER
--------------
ABBCC EEE PPDD

1.5 LPAD(str1,num,str2)，如果str1的长度小于num，则用str2从前边补齐，补齐后的长度等于num，如果str1的长度小于num，则从最后一位向前截取，直到截取后的str1的长度等于num，如果str1的长度和num相同，则结果显示str1.

      RPAD()

SQL> select lpad('China',10,'*') LPAD from dual;

LPAD
----------
*****China

SQL> select lpad('AAA',10,'bc') LPAD from dual;

LPAD
----------
bcbcbcbAAA

SQL> select LPAD('GreatWall',5,'*') LPAD from dual;

LPAD
-----
Great

SQL> select LPAD('GreatWall',9,'*') LPAD from dual;

LPAD

---------
GreatWall

  RPAD()函数。

SQL> select rpad('GreatWall',10,'*') from dual;

RPAD
----------
GreatWall*

1.6 LTRIM()和RTRIM()

SQL> select ltrim('***ABC****','*') from dual;

LTRIM
-------
ABC****

SQL> select rtrim('****ABC****','*') from dual;

RTRIM
-------
****ABC

SQL> select ltrim(rtrim('****ABC****','*'),'*') from dual;

LTR
---
ABC

用LTRIM()函数去掉左边的空格。

SQL> select ltrim('  AB',' ') from dual;

LT
--
AB

1.7 REPLACE()函数

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

SQL> SELECT REPLACE('  A  B  C  ','  ','') FROM dual;

REP
---
ABC

 

1.8 SUBSTR()

SQL> select substr('2012/10/22',6,2) month from dual;

MO
--
10

SQL> SELECT SUBSTR('ABCDEFGH',5,4) FROM DUAL;

SUBS
----
EFGH

SQL> select substr('Oracle Database',8) from dual;

SUBSTR
--------
Database

SQL> select substr('Oracle',-3,3) from dual;

SUB
---
cle