字符处理函数

 说明：

可转成string的表达式：执行结果为数值类型、日期类型、布尔类型、BINARY、CHAR、VARCHAR或者VARCHAR2的表达式。

可转成int的表达式：执行结果为数值类型、布尔类型、BINARY、CHAR、VARCHAR或者VARCHAR2的表达式。

函数支持操作CLOB、BLOB类型数据的最大支持操作65534字节（length、lengthb函数操作CLOB、BLOB类型数据无长度约束）。

CONCAT

语法：

CONCAT(str[,...])

功能：拼接字符串。可用于将从不同字段获取的资料串连在一起输出。

该函数可拼接一个或多个字符串，多个字符串之间以逗号“,”分割，返回结果为拼接各个参数所产生的字符串。

如果一个参数是NULL，CONCAT函数则忽略该参数；但是，如果NULL使用单引号括起来，CONCAT函数则会将NULL作为字符串处理。该函数可嵌套使用。

入参是字符串或可转换成字符串的表达式；返回值是字符串。

说明：返回值最大支持8000字节，超过则报错。

示例：

拼接三个字符串，其中一个参数是NULL，该NULL值被忽略。

SELECT CONCAT('11',NULL,'22');

CONCAT('11',NULL,'22')

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

1122

1 rows fetched.

拼接三个字符串，其中一个字符串是NULL，该字符串不会被忽略。

SELECT CONCAT('11','NULL','22');

CONCAT('11','NULL','22')

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

11NULL22

1 rows fetched.

CONCAT_WS

语法：

CONCAT_WS(separator, str1, str2,...)

功能：拼接一个或多个字符串，多个字符串之间以separator分隔，返回结果为拼接各个参数所产生的字符串。

如果参数是NULL，则CONCAT_WS函数忽略该参数；如果NULL用单引号括起来，则CONCAT_WS函数会将NULL作为字符串处理。

该函数可嵌套使用。

入参是字符串或可转换成字符串的表达式；返回值是字符串。

说明：返回值最大支持8000字节，超过则报错。

示例：

拼接三个字符串，其中一个参数是NULL，该NULL值被忽略。

SELECT CONCAT_WS('-','11',NULL,'22');

CONCAT_WS('-','11',NULL,'22')

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

11-22

1 rows fetched.

DBMS_LOB.SUBSTR

语法：

DBMS_LOB.SUBSTR(str[,len[,start]])

功能：字符串截取函数。截取并返回str中从|start|开始的len个字节的子串。

参数start表示索引位置，取值范围是正整数，表示从左向右第|start|个字节位置。

len表示自索引位置开始向右截取的字节数。len≤0时，函数DBMS_LOB.SUBSTR的返回值为空。

入参str必须是可转成STRING的表达式，入参start、len必须是可转成INT的表达式；返回值是STRING。

示例：

从第一个字符开始，截取并返回4个字符。

select  DBMS_LOB.SUBSTR(123456,4,1) FROM dual;

DBMS_LOB.SUBSTR(123456,4,1)

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

1234

1 rows fetched.

FIND_IN_SET

语法：

FIND_IN_SET(sub,src)

功能：返回sub在src中的索引位置。

sub为元素字符串。src为集合字符串，元素之间用逗号隔开。

入参是可转成字符串的表达式；返回值是INTEGER。

说明：

当前不支持对clob和blob数据类型的处理。

示例：

返回字符串“b”在字符串“a，b，c，d”中的索引位置。

SELECT FIND_IN_SET('b','a,b,c,d');

FIND_IN_SET('B','A,B,C,D')

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

2

1 rows fetched.

HEX

语法：

HEX(p1)

功能：返回十六进制值的字符串表示形式。

入参p1可为数值型或STRING类型；返回值是STRING。

示例：

返回字符串“abc”的十六进制字符串表示形式和数字255的十六进制数值表示形式。

SELECT HEX('abc'), HEX(255) FROM DUAL;

HEX('ABC') HEX(255)

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

616263     FF

1 rows fetched.

HEX2BIN

语法：

HEX2BIN(str)

功能：返回十六进制字符串所表示的字节串。

返回值是BINARY。

说明：输入的十六进制字符串必须以"0x"作为前缀。

示例：

返回十六进制字符串“0x39”所表示的字节串。

SELECT HEX2BIN('0x39') FROM DUAL;

HEX2BIN('0X39')

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

9

1 rows fetched.

HEXTORAW

语法：

HEXTORAW(str)

功能：返回十六进制字符串所表示的字节串。

返回值是RAW。

示例：

返回十六进制字符串“abcdef”所表示的字节串。

SELECT HEXTORAW('abcdef') FROM DUAL;

HEXTORAW('ABCDEF')

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

ABCDEF

1 rows fetched.

INSERT

语法：

INSERT(str,pos,len,newstr)

功能：返回字符串str，并且将自pos位置开始、长度为len的字符串替换成newstr。

如果pos不在字符串str的长度内，则返回原始字符串。

如果参数len的值大于自起始位置pos开始的其余字符串长度，则将自起始位置pos开始的所有字符替换为字符串newstr。

入参str和newstr都是可转成STRING的表达式，入参pos和len都是可转成INTEGER的表达式；返回值是STRING。

说明：

该函数处理的是字符，返回值最大支持8000字节。

示例：

返回字符串“Quadratic”，并且将自第3个字符开始的4个字符替换成新字符串“What”。

SELECT INSERT('Quadratic', 3, 4, 'What');

INSERT('QUADRATIC', 3, 4, 'WHAT')

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

QuWhattic

1 rows fetched.

返回字符串“Quadratic”，并且将自第10个字符开始的4个字符替换成新字符串“What”。在该示例中，起始位置pos不在字符串“Quadratic”的长度内，因此返回原始字符串。

SELECT INSERT('Quadratic', 10, 4, 'What');

INSERT('QUADRATIC', 10, 4, 'WHAT')

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

Quadratic

1 rows fetched.

返回字符串“Quadratic”，并且将自第3个字符开始的100个字符替换成新字符串“What”。在该示例中，参数len的值大于自起始位置pos开始的其余字符串长度，因此将自起始位置pos开始的所有字符替换为字符串newstr。

SELECT INSERT('Quadratic', 3, 100, 'What');

INSERT('QUADRATIC', 3, 100, 'WHAT')

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

QuWhat

1 rows fetched.

INSTR

语法：

INSTR(str1,str2[,pos[,n]])

功能：字符串查找函数。返回要查找的字符串在源字符串中的位置，按字符计算位置。

str1是源字符串，str2是要查找的字符串，pos是索引位置，代表从str1的哪个位置开始查找字符串str2，该参数是可选参数，取值为非零整数，如果省略则默认为1；n代表要查找第几次出现的str2，该参数也是可选参数，取值为正整数，如果省略则默认为1。

在源字符串str1中，INSTR函数按照从左向右（pos为正整数时）或从右向左（pos为负整数时）的顺序，从第|pos|个字符位置开始搜索字符串str2，直到str2第n次出现时，返回str2首字符在源字符串str1中所在的字符位置，如果没有搜索到第n次出现的str2则返回0。无论搜索顺序是从左向右还是从右向左，计算要查找的字符串在源字符串中的位置都是从左向右计算。

入参str1、str2均为可转成STRING的表达式，入参pos、n均为可转成INT的表达式；返回值是INT。

示例：

从第3个字符开始，从左向右在字符串“oracleor”中搜索字符串“or”第2次出现的位置，因搜索不到而返回0。

SELECT INSTR('oracleor','or', 3, 2) POSITION FROM DUAL;

POSITION

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

1 rows fetched.

从倒数第1个字符开始，从右向左在字符串“oracleor”中搜索字符串“or”第2次出现的位置。

SELECT INSTR('oracleor','or', -1, 2) POSITION FROM DUAL;

POSITION

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

1

1 rows fetched.

INSTRB

语法：

INSTRB(str1,str2[,pos[,n]])

功能：字符串查找函数。返回要查找的字符串在源字符串中的位置，按字节计算位置。

str1是源字符串，要在此字符串中查找。

str2是要在str1中查找的字符串。

入参str1、str2均为可转成STRING的表达式，入参pos、n均为可转成INT的表达式；返回值是INT。

pos是索引位置，代表从str1的哪个位置开始查找字符串str2，该参数是可选参数，取值为非零整数，如果省略则默认为1。

n代表要查找第几次出现的str2，该参数也是可选参数，取值为正整数，如果省略则默认为1。

说明：

在源字符串str1中，INSTRB函数按照从左向右（pos为正整数时）或从右向左（pos为负整数时）的顺序，从第|pos|个字符位置开始搜索字符串str2，直到str2第n次出现时，返回str2首字符在源字符串str1中所在的字符位置，如果没有搜索到第n次出现的str2则返回0。

无论搜索顺序是从左向右还是从右向左，计算要查找的字符串在源字符串中的位置都是从左向右计算。

示例：

从第3个字节开始，从左向右在字符串oracleor中搜索字符串or第1次出现的位置，第一个搜索指定参数n的值，第二个搜索不指定参数n的值。

SELECT INSTRB('oracleor','or', 3, 1) POSITION_WITH_n, INSTRB('oracleor','or', 3) POSITION_WITHOUT_n FROM DUAL;

POSITION_WITH_N POSITION_WITHOUT_N

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

7               7

1 rows fetched.

从第1个字节开始，从左向右分别按字节和字符搜索字符串“A”在字符串“我A”中第1次出现的位置。

SELECT INSTRB('我A','A',1,1)AS BYTE_POSITION,INSTR('我A','A',1,1) AS WORD_POSITION FROM DUAL; 

BYTE_POSITION WORD_POSITION

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

4             2

1 rows fetched.

INET_ATON

语法：

INET_ATON(str)

功能：将给定的网络地址转换为数字地址。

入参是点分十进制字符串。

示例：

将192.168.1.1转化为数字地址。

SQL> SELECT INET_ATON('192.168.1.1') FROM DUAL;

INET_ATON('192.168.1.1')

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

3232235777

1 rows fetched.

LEFT

语法：

LEFT(str,length)

功能：返回指定字符串的左边几位字符。

str是要提取子字符串的字符串。不支持CLOB类型的字符串。

length是一个正整数，指定从左边返回的字符个数。

说明：

如果length为0或负数，则LEFT函数返回一个空字符串。

如果length大于str字符串的长度，则LEFT函数返回整个str字符串。

目前客户端对字符串最大支持为32767字节，故函数返回值最大为32767字节。

示例：

返回字符串“abcdefg”左边的3个字符串。

SQL> select left('abcdefg', 3) from dual;

LEFT('ABCDEFG', 3)

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

abc

1 rows fetched.

LENGTH

语法：

LENGTH(str)

功能：获取字符串长度函数。该函数返回str的字符数。

入参是可转成STRING的表达式；返回值是INT。

示例：

获取字符串“my score is 90”的字节长度。

SELECT LENGTH('my score is 90') AS BYTE_LENGTH FROM DUAL;

BYTE_LENGTH

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

14

1 rows fetched.

LENGTHB

语法：

LENGTHB(str)

功能：获取字符串长度函数。该函数返回str的字节数。

入参是可转成STRING的表达式；返回值是INT。

示例：

获取字符串“my score is 90”的字符长度。

SELECT  LENGTHB('my score is 90') AS WORD_LENGTH FROM DUAL;

WORD_LENGTH

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

14

1 rows fetched.

LOCATE

语法：

LOCATE(substr,str[,pos])

功能：返回字符串str中子字符串substr第一次出现的位置，起始位置是pos，参数pos省略时起始位置是第一个字符。如果自起始位置pos后，substr不在str中，则返回0。

入参substr和str是可转成STRING的表达式，入参pos是可转成INTEGER的表达式；返回值是INTEGER。

说明：

该函数处理的是字符。

示例：

返回字符串“foobarbar”中子字符串“bar”第一次出现的位置，起始位置是第5个字符。

SELECT LOCATE('bar', 'foobarbar', 5);

LOCATE('BAR', 'FOOBARBAR', 5)

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

7

1 rows fetched.

子字符串“xbar”不在字符串“foobar”中，返回0。

SELECT LOCATE('xbar', 'foobar');

LOCATE('XBAR', 'FOOBAR')

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

1 rows fetched.

LOWER

语法：

LOWER(str)

功能：将字符串转换成对应字符的小写。

入参是可转成STRING的表达式；返回值是STRING。

示例：

将字符串“MR. SCOTT MCMILLAN”的所有字符转换为小写字符。

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL;

Lowercase

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

mr. scott mcmillan

1 rows fetched.

LPAD

语法：

LPAD(str,pad_len[,pad_str])

功能：从左边对字符串使用指定的字符进行填充，直至字符串长度达到指定长度。可使用此函数格式化查询的输出。

入参str是源字符串，表示准备被填充的字符串（可转成STRING的表达式）；入参pad_len是所返回字符串的长度，即填充后的字符串长度（若pad_len<=str长度，则将str截取从左往右的pad_len个字节）；入参pad_str表示填充字符串，如果未指定该参数，则默认为空字符。返回值是STRING。

说明：

返回值最大支持8000字节。

示例：

打印员工编号和员工姓名，对员工姓名进行左侧填充至20个字符。

--删除表employee_2017。 

DROP TABLE IF EXISTS employee_2017;

--创建表employee_2017。 

CREATE TABLE employee_2017(employee_id INT NOT NULL,first_name VARCHAR(10),last_name VARCHAR(10), hire_date DATETIME);

--加入记录1。

INSERT INTO employee_2017(employee_id,first_name,last_name,hire_date) VALUES(1001,'Alice','BROWN','2017-06-20 12:00:00');

--加入记录2。

INSERT INTO employee_2017(employee_id,first_name,last_name,hire_date) VALUES(102,'BOB','Smith','2017-10-20 12:00:00');

--加入记录3。

INSERT INTO employee_2017(employee_id,first_name,last_name,hire_date) VALUES(13,'ALAN','Jones','2017-05-10 12:00:00');

--提交事务。

COMMIT;

--对员工姓名进行左侧填充至20个字符。

SELECT employee_id, LPAD (last_name, 20, '.') LAST_NAME FROM employee_2017 ORDER BY last_name;

EMPLOYEE_ID  LAST_NAME

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

1001         ...............BROWN

13           ...............Jones

102          ...............Smith

3 rows fetched.