Oracle 11g Release 1 (11.1) 单行函数——（返回字符值）字符函数

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#autoId2

本文内容

返回字符值的字符函数
附录 ASCII

Oracle 字符函数包括：

返回字符值的字符函数
NLS 字符函数
返回数字值的字符函数

返回字符值的字符函数

返回字符值的字符函数会返回如下数据类型：

若输入参数为 CHAR 或 VARCHAR2 类型，则返回值为 VARCHAR2 类型。
若输入参数为 NCHAR 或 NVARCHAR2 类型，则返回值为 NVARCHAR2 类型。

函数返回值的长度由返回数据类型的最大长度决定：

对于返回 CHAR 或 VARCHAR2 的函数，若返回值的长度超过限制，则 Oracle 数据库截取并返回，不报错。
对于返回 CLOB 的函数，若返回值的长度超过限制，则 Oracle 数据库什么都不返回，并抛出异常。

CHR

CONCAT

INITCAP

LOWER

LPAD

LTRIM

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

REGEXP_REPLACE

REGEXP_SUBSTR

REPLACE

RPAD

RTRIM

SOUNDEX

SUBSTR

TRANSLATE

TREAT

TRIM

UPPER

CHR(n)

CHR(n USING NCHAR_CS)

按数据库字符集，返回与 n 二进制等价的 VARCHAR2 字符。它们（参数和返回值）的二进制相同。若指定 USING NCHAR_CS，则按国际化字符集。

对于单字节字符集（single-byte character sets），如果 n>256，则 Oracle 数据库返回与 n 等价的二进制与 256 的模。对于多字节字符集（multibyte character sets），n 必须解决整个代码点。不验证无效的代码点，并且指定无效代码点的结果是不确定的。

该函数参数为 NUMBER 类型，或是任何可以隐式转换成 NUMBER，并返回一个字符。

CONCAT(char1,char2)

连接函数。char1 和 char2 可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB。返回的字符串值与 char1 的字符集相同，而类型依赖于参数的数据类型。

当连接两个不同的数据类型时，Oracle 数据库无损转换返回数据。因此，若其中一个参数为 LOB，则返回值为 LOB。若其中一个参数为国际数据类型，则返回值也是一个国际化数据类型。例如：

CONCAT(CLOB, NCLOB)，返回 NCLOB
CONCAT(NCLOB, NCHAR)，返回 NCLOB
CONCAT(NCLOB, CHAR)，返回 NCLOB
CONCAT(NCHAR, CLOB)，返回 NCLOB

该函数等价于连接运算符（||）。

INITCAP(char)

LOWER(char)

UPPER(char)

NLS_INITCAP(char)

NLS_INITCAP(char,’nlsparam’)

NLS_LOWER(char)

NLS_LOWER(char,’nlsparam’)

NLS_UPPER(char)

NLS_UPPER(char,’nlsparam’)

INITCAP 头字母转换成大写
LOWER 所有字符转换成小写
UPPER 所有字符转换成大写
NLS_* 开头函数可以指定对特定语言敏感的大小写。

示例 1：演示对特定语言敏感。英语的习惯是头字母大写，但其他语言，就不一定了。

SQL> SELECT NLS_INITCAP('ijsland') "InitCap",

  2  NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') as "linguistic-sensitiv"

  3  FROM DUAL;

InitCap linguis

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

Ijsland IJsland

SQL>

LPAD(expr1,n)

LPAD(expr1,n,expr2)

RPAD(expr1,n)

RPAD(expr1,n,expr2)

LPAD 左补齐
RPAD 右补齐

示例 2：演示 LPAD 和 RPAD。

SQL> SELECT LPAD('Page 1', 15),

  2         LPAD('Page 1', 15, '*$'),

  3         RPAD('Page 1', 15),

  4         RPAD('Page 1', 15, '*$')

  5    FROM DUAL;

LPAD('PAGE1',15 LPAD('PAGE1',15 RPAD('PAGE1',15 RPAD('PAGE1',15

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

         Page 1 *$*$*$*$*Page 1 Page 1          Page 1*$*$*$*$*

SQL>

LTRIM(char,set)

LTRIM(char)

RTRIM(char,set)

RTRIM(char)

TRIM(trim_source)

TRIM(trim_character FROM trim_source)

TRIM(LEADING,trim_character FROM trim_source)

TRIM(TRAILING,trim_character FROM trim_source)

TRIM(BOTH,trim_character FROM trim_source)

LTRIM 从最左边开始移除
RTRIM 从最右边开始移除
TRIM 从左右移除

示例 3：演示 LTRIM 和 RTRIM。

SQL> SELECT 'Page 1' as "src",

  2         LTRIM('Page 1', 'P') as "ltrim 'P'",

  3         LTRIM('Page 1', 'Pa') as "ltrim 'Pa'",

  4         RTRIM('Page 1', 'a') as "rtrim 'P'"

  5    FROM DUAL;

src    ltrim 'P'  ltrim 'Pa' rtrim 'P'

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

Page 1 age 1      ge 1       Page 1

SQL>

示例 4：演示 TRIM。表 employees 位于 Oracle OE schema。

SQL> SELECT employee_id,

  2         hire_date as "hiredate",

  3         TO_CHAR(TRIM(0 FROM hire_date)) as "Trim 0",

  4         TO_CHAR(TRIM(LEADING 0 FROM hire_date)) as "TrimLeading 0",

  5         TO_CHAR(TRIM(TRAILING 0 FROM hire_date)) as "TrimTrailing 0",

  6         TO_CHAR(TRIM(BOTH 0 FROM hire_date)) as "TrimBoth 0"

  7    FROM employees

  8   WHERE department_id = 60

  9   ORDER BY employee_id;

EMPLOYEE_ID hiredate   Trim 0     TrimLeadin TrimTraili TrimBoth 0

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

        103 03-1月 -90 3-1月 -9   3-1月 -90  03-1月 -9  3-1月 -9

        104 21-5月 -91 21-5月 -91 21-5月 -91 21-5月 -91 21-5月 -91

        105 25-6月 -97 25-6月 -97 25-6月 -97 25-6月 -97 25-6月 -97

        106 05-2月 -98 5-2月 -98  5-2月 -98  05-2月 -98 5-2月 -98

        107 07-2月 -99 7-2月 -99  7-2月 -99  07-2月 -99 7-2月 -99

SQL>

注意：结果日期中 0 的变化。

NLSSORT(char,’nlsparam’)

NLSSORT(char)

指定对特定语言敏感的排序。

示例 5：演示 NLSSORT。

先创建需要的表 test，并插入数据。代码如下：

SQL> CREATE TABLE test (name VARCHAR2(15));

表已创建。

SQL> INSERT INTO test VALUES ('Gaardiner');

已创建 1 行。

SQL> INSERT INTO test VALUES ('Gaberd');

已创建 1 行。

SQL> INSERT INTO test VALUES ('Gaasten');

已创建 1 行。

SQL>

SQL> SELECT * FROM test ORDER BY name;

NAME

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

Gaardiner

Gaasten

Gaberd

SQL>

若指定语言敏感排序，则就与英语的英文排序不同。代码如下：

SQL> SELECT * FROM test ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

NAME

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

Gaberd

Gaardiner

Gaasten

SQL>

示例 6：演示在比较操作中使用 NLSSORT。

SQL> SELECT * FROM test WHERE name > 'Gaberd'

  2  ORDER BY name;

为选定行

SQL> SELECT * FROM test WHERE NLSSORT(name, 'NLS_SORT = XDanish') >

  2  NLSSORT('Gaberd', 'NLS_SORT = XDanish')

  3  ORDER BY name;

NAME

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

Gaardiner

Gaasten

SQL>

REPLACE(char,search_string)

REPLACE(char,search_string,replacement_string)

REGEXP_REPLACE(source_char,pattern)

REGEXP_REPLACE(source_char,pattern,replace_string)

REGEXP_REPLACE(source_char,pattern,replace_string,position)

REGEXP_REPLACE(source_char,pattern,replace_string,position,occurrence)

REGEXP_REPLACE(source_char,pattern,replace_string,position,occurrence,match_param)

REPLACE 按字符（串）替换。
REGEXP_REPLACE 按正则表达式替换。

示例中的表 employees 和 countries 位于 Oracle OE schema。

示例 7：演示 REPLACE。

SQL> SELECT REPLACE('JACK and JUE', 'J') "remove",

  2         REPLACE('JACK and JUE', 'J', 'BL') "replace"

  3    FROM DUAL;

remove     change

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

ACK and UE BLACK and BLUE

SQL>

示例 8：演示 REGEXP_REPLACE。检索 phone_number 电话号码字段中形如 xxx.xxx.xxxx 格式的号码，并重新格式化为 (xxx) xxx-xxxx。

SQL> SELECT phone_number,

  2         REGEXP_REPLACE(phone_number,

  3                        '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4',

  4                        '(\1) \2-\3') "REGEXP_REPLACE"

  5    FROM employees

  6   ORDER BY "REGEXP_REPLACE";

PHONE_NUMBER         REGEXP_REPLACE

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

515.123.4444         (515) 123-4444

515.123.4567         (515) 123-4567

515.123.4568         (515) 123-4568

515.123.4569         (515) 123-4569

515.123.5555         (515) 123-5555

515.123.7777         (515) 123-7777

515.123.8080         (515) 123-8080

515.123.8181         (515) 123-8181

515.123.8888         (515) 123-8888

515.124.4169         (515) 124-4169

515.124.4269         (515) 124-4269

515.124.4369         (515) 124-4369

515.124.4469         (515) 124-4469

515.124.4567         (515) 124-4567

515.124.4569         (515) 124-4569

515.127.4561         (515) 127-4561

515.127.4562         (515) 127-4562

515.127.4563         (515) 127-4563

515.127.4564         (515) 127-4564

515.127.4565         (515) 127-4565

515.127.4566         (515) 127-4566

590.423.4560         (590) 423-4560

590.423.4567         (590) 423-4567

590.423.4568         (590) 423-4568

590.423.4569         (590) 423-4569

590.423.5567         (590) 423-5567

603.123.6666         (603) 123-6666

650.121.1234         (650) 121-1234

650.121.1834         (650) 121-1834

650.121.2004         (650) 121-2004

650.121.2019         (650) 121-2019

650.121.2034         (650) 121-2034

650.121.2874         (650) 121-2874

650.121.2994         (650) 121-2994

650.121.8009         (650) 121-8009

650.123.1234         (650) 123-1234

650.123.2234         (650) 123-2234

650.123.3234         (650) 123-3234

650.123.4234         (650) 123-4234

650.123.5234         (650) 123-5234

650.124.1214         (650) 124-1214

650.124.1224         (650) 124-1224

650.124.1334         (650) 124-1334

650.124.1434         (650) 124-1434

650.124.5234         (650) 124-5234

650.124.6234         (650) 124-6234

650.124.7234         (650) 124-7234

650.124.8234         (650) 124-8234

650.127.1634         (650) 127-1634

650.127.1734         (650) 127-1734

650.127.1834         (650) 127-1834

650.127.1934         (650) 127-1934

650.501.1876         (650) 501-1876

650.501.2876         (650) 501-2876

650.501.3876         (650) 501-3876

650.501.4876         (650) 501-4876

650.505.1876         (650) 505-1876

650.505.2876         (650) 505-2876

650.505.3876         (650) 505-3876

650.505.4876         (650) 505-4876

650.507.9811         (650) 507-9811

650.507.9822         (650) 507-9822

650.507.9833         (650) 507-9833

650.507.9844         (650) 507-9844

650.507.9876         (650) 507-9876

650.507.9877         (650) 507-9877

650.507.9878         (650) 507-9878

650.507.9879         (650) 507-9879

650.509.1876         (650) 509-1876

650.509.2876         (650) 509-2876

650.509.3876         (650) 509-3876

650.509.4876         (650) 509-4876

011.44.1343.329268   011.44.1343.329268

011.44.1343.529268   011.44.1343.529268

011.44.1343.629268   011.44.1343.629268

011.44.1343.729268   011.44.1343.729268

011.44.1343.829268   011.44.1343.829268

011.44.1343.929268   011.44.1343.929268

011.44.1344.129268   011.44.1344.129268

011.44.1344.345268   011.44.1344.345268

011.44.1344.429018   011.44.1344.429018

011.44.1344.429268   011.44.1344.429268

011.44.1344.429278   011.44.1344.429278

011.44.1344.467268   011.44.1344.467268

011.44.1344.478968   011.44.1344.478968

011.44.1344.486508   011.44.1344.486508

011.44.1344.498718   011.44.1344.498718

011.44.1344.619268   011.44.1344.619268

011.44.1344.987668   011.44.1344.987668

011.44.1345.429268   011.44.1345.429268

011.44.1345.529268   011.44.1345.529268

011.44.1345.629268   011.44.1345.629268

011.44.1345.729268   011.44.1345.729268

011.44.1345.829268   011.44.1345.829268

011.44.1345.929268   011.44.1345.929268

011.44.1346.129268   011.44.1346.129268

011.44.1346.229268   011.44.1346.229268

PHONE_NUMBER         REGEXP_REPLACE

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

011.44.1346.329268   011.44.1346.329268

011.44.1346.529268   011.44.1346.529268

011.44.1346.629268   011.44.1346.629268

011.44.1346.729268   011.44.1346.729268

011.44.1644.429262   011.44.1644.429262

011.44.1644.429263   011.44.1644.429263

011.44.1644.429264   011.44.1644.429264

011.44.1644.429265   011.44.1644.429265

011.44.1644.429266   011.44.1644.429266

011.44.1644.429267   011.44.1644.429267

已选择107行。

SQL>

示例 9：演示 REGEXP_REPLACE。检查 country_name 字段，为每个非空的字段值，在字符间都插入空格。

SQL> SELECT

  2  REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"

  3  FROM countries;

REGEXP_REPLACE

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

A r g e n t i n a

A u s t r a l i a

B e l g i u m

B r a z i l

C a n a d a

SQL>

示例 10：演示 REGEXP_REPLACE。检查字符串，查找两个或更多空格。用一个空格替换掉多余的空格。

SQL> SELECT

  2 REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',

  3                '( ){2,}', ' ') "REGEXP_REPLACE"

  4 FROM DUAL;

REGEXP_REPLACE

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

500 Oracle Parkway, Redwood Shores, CA

SQL>

SUBSTR(char,position)

SUBSTR(char,position,substring_length)

SUBSTRB(char,position)

SUBSTRB(char,position,substring_length)

SUBSTRC(char,position)

SUBSTRC(char,position,substring_length)

SUBSTR2(char,position)

SUBSTR2(char,position,substring_length)

SUBSTR4(char,position)

SUBSTR4(char,position,substring_length)

REGEXP_SUBSTR(source_char,pattern)

REGEXP_SUBSTR(source_char,pattern,position)

REGEXP_SUBSTR(source_char,pattern,position,occurrence)

REGEXP_SUBSTR(source_char,pattern,position,occurrence,match_param)

REGEXP_SUBSTR(source_char,pattern,position,occurrence,match_param,subexpr)

SUBSTR* 按位置匹配子字符串。
REGEXP_SUBSTR* 按正则表达匹配子字符串。

示例 11：演示 SUBSTR 和 REGEXP_SUBSTR。

SQL> SELECT SUBSTR('ABCDEFG', 3, 4) as "pos34sub",

  2         SUBSTR('ABCDEFG', -5, 4) as "pos-54sub",

  3         REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') "

REGEXPR_SUBSTR"

  4    FROM DUAL;

pos3 pos- REGEXPR_SUBSTR

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

CDEF CDEF , Redwood Shores,

SQL>

SOUNDEX(char)

可以让你比较这样的单词，它们的拼写不同，但英语的读音很像。

示例 12：演示 SOUNDEX。

SQL> SELECT last_name, first_name

  2    FROM hr.employees

  3   WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE')

  4   ORDER BY last_name, first_name;

LAST_NAME                 FIRST_NAME

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

Smith                     Lindsey

Smith                     William

SQL>

TRANSLATE(expr,from_string,to_string)

字符串中替换。把 from_string 替换成 to_string。

示例 13：演示 TRANSLATE。

SQL> SELECT TRANSLATE('SQL*Plus User''s Guide', ' *', '__'),

  2         TRANSLATE('SQL*Plus User''s Guide', ' */''', '___')

  3    FROM DUAL;

TRANSLATE('SQL*PLUSUS TRANSLATE('SQL*PLUSU

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

SQL_Plus_User's_Guide SQL_Plus_Users_Guide

SQL>

说明

前者，将字符 space、星号 * 都替换成 _；
后者，将字符 space、星号 *、单引号 '，都替换成 _。

TREAT

该函数改变表达式为已经声明的类型。

该函数必须具有 EXECUTE object 权限。

附录 ASCII

NUL

SOH

STX

ETX

EOT

ENQ

ACK

BEL

DLE

DC1

DC2

DC3

DC4

NAK

SYN

ETB

CAN

SUB

ESC

(

)

;

[

]

{

}

DEL

（备注：十六进制表，如十六进制 41 为 A）

posted @ 2012-07-22 23:03 船长&CAP 阅读(620) 评论(0) 编辑收藏举报

刷新页面返回顶部

船长&CAP

“0 + 1 = The World, 我们既愚蠢/也聪明/愚蠢的是/我们世界只有0和1/聪明的是/我们却用0和1描述了这个世界”