字符处理函数
- 可转成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') ---------------------- 1 rows fetched.- 拼接三个字符串，其中一个字符串是NULL，该字符串不会被忽略。
SELECT CONCAT('11','NULL','22'); CONCAT('11','NULL','22') ------------------------ 11NULL22 1 rows fetched.CONCAT_WS
语法：
CONCAT_WS(separator, str1, str2,...)功能：拼接一个或多个字符串，多个字符串之间以逗号分隔，返回结果为拼接各个参数所产生的字符串。
- 如果参数是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 SYS_DUMMY; DBMS_LOB.SUBSTR('123456',4,1) --------------------------- 1 rows fetched.FIND_IN_SET
语法：
FIND_IN_SET(sub,src)功能：返回sub在src中的索引位置。
- sub为元素字符串，src为集合字符串，元素之间用逗号隔开。
- 入参是可转成字符串的表达式；返回值是INTEGER。
- 如果集合字符串查找失败，返回0，否则返回元素所在位置，从1开始计数。
- 空格也作为字符串的一部分。
当前不支持对clob和blob数据类型的处理。
返回字符串“b”在字符串“a，b，c，d”中的索引位置。SELECT FIND_IN_SET('b','a,b,c,d'); FIND_IN_SET('B','A,B,C,D') -------------------------- 1 rows fetched.HEX
语法：
HEX(p1)功能：返回十六进制值的字符串表示形式。
入参p1可为数值型或STRING类型；返回值是STRING。
返回字符串“abc”的十六进制字符串表示形式和数字255的十六进制数值表示形式。SELECT HEX('abc'), HEX(255) FROM SYS_DUMMY; HEX('ABC') HEX(255) ---------- -------- 616263 FF 1 rows fetched.HEX2BIN
语法：
HEX2BIN(str)功能：返回十六进制字符串所表示的字节串。
返回值是BINARY。
说明：输入的十六进制字符串必须以"0x"作为前缀。
返回十六进制字符串“0x39”所表示的字节串。SELECT HEX2BIN('0x39') FROM SYS_DUMMY; HEX2BIN('0X39') ---------------------------------------------------------------- 1 rows fetched.HEXTORAW
语法：
HEXTORAW(str)功能：返回十六进制字符串所表示的字节串。
返回值是RAW。
返回十六进制字符串“abcdef”所表示的字节串。SELECT HEXTORAW('abcdef') FROM SYS_DUMMY; 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。
- 从第1个字符开始，从左向右在字符串“gaussdb”中搜索字符串“au”第1次出现的位置。
SELECT INSTR('gaussdb','au', 1, 1) POSITION FROM SYS_DUMMY; POSITION ------------ 1 rows fetched.- 从倒数第1个字符开始，从右向左在字符串“gaussdb”中搜索字符串“db”第1次出现的位置。
SELECT INSTR('gaussdb','db', -1, 1) POSITION FROM SYS_DUMMY; POSITION ------------ 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 SYS_DUMMY; 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 SYS_DUMMY; 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 SYS_DUMMY; 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 SYS_DUMMY; LEFT('ABCDEFG', 3) ------------------ 1 rows fetched.LENGTH
语法：
LENGTH(str)功能：获取字符串长度函数。该函数返回str的字符数。
入参是可转成STRING的表达式；返回值是INT。
说明：如果输入的参数为utf-8类型，报错Nls internal error, invalid utf-8 buffer, 请使用LENGTHB函数
获取字符串“我的成绩是90分”的字符长度。SELECT LENGTH('my score is 90') AS BYTE_LENGTH FROM SYS_DUMMY; BYTE_LENGTH ------------ 1 rows fetched.LENGTHB
语法：
LENGTHB(str)功能：获取字符串长度函数。该函数返回str的字节数。
入参是可转成STRING的表达式；返回值是INT。
说明：如果输入参数为CHAR类型，其功能等同于LENGTH。
获取字符串“我的成绩是90分”的字节长度。SELECT LENGTHB('我的成绩是90分') AS WORD_LENGTH FROM SYS_DUMMY; WORD_LENGTH ------------ 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) ----------------------------- 1 rows fetched.- 子字符串“xbar”不在字符串“foobar”中，返回0。
SELECT LOCATE('xbar', 'foobar'); LOCATE('XBAR', 'FOOBAR') ------------------------ 1 rows fetched.LOWER
语法：
LOWER(str)功能：将字符串转换成对应字符的小写，支持全球化语言。
入参是可转成STRING的表达式，返回值是STRING。
将希腊文字符串“Α Β Γ Δ Ε Ζ Η Θ Ι Κ Λ Μ Ν Ξ Ο Π Ρ Σ Τ Υ Φ Χ Ψ Ω ”的所有字符转换为小写字符。select lower('Α Β Γ Δ Ε Ζ Η Θ Ι Κ Λ Μ Ν Ξ Ο Π Ρ Σ Τ Υ Φ Χ Ψ Ω ') lower; LOWER ---------------------------------------------------------------- α β γ δ ε ζ η θ ι κ λ μ ν ξ ο π ρ σ τ υ φ χ ψ ω 1 rows fetched.将字符串“ABCDEFG”的所有字符转换为小写字符。SELECT LOWER('ABCDEFG') "Lower" FROM SYS_DUMMY; Lower ------------------ abcdefg 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.LTRIM(str)
语法：
LTRIM(str[,set])功能：删除字符串左侧的空格或其他预定义字符。可使用此函数格式化查询的输出。
- 该函数从str的左端删除set中出现的所有字符，如果未指定set，则默认为删除空格。
- 如果str是字符型数据，则必须将其括在单引号中。 LTRIM函数会查找str最左端的字符是否包含在set中，如果包含则删掉该字符，直到str最左端的字符不包含在set中时停止搜索。
- 入参是可转成STRING的表达式，返回值是STRING。
当前不支持对CLOB数据和BLOB数据的处理。
- 从字符串“<=====>GAUSSDB <=====>”的最左侧删除小于号（<）、大于号（>）和等于号（=）。
SELECT LTRIM('<=====>GAUSSDB <=====>', '<>=') "LTRIM Example" FROM SYS_DUMMY; LTRIM Example ------------------ GAUSSDB <=====> 1 rows fetched.- 不指定参数set，因此从字符串“ GAUSSDB”的最左侧删除空格。
SELECT LTRIM(' GAUSSDB') "LTRIM Example" FROM SYS_DUMMY; LTRIM Example ------------- GAUSSDB 1 rows fetched.- 从字符串“ GAUSSDB”的最左侧删除字母“G”和字母“A”。
SELECT LTRIM('GAUSSDB', 'GA') "LTRIM Example" FROM SYS_DUMMY; LTRIM Example --------------- USSDB 1 rows fetched.REGEXP_INSTR
语法：
REGEXP_INSTR(str,pattern[,position[,occurrence[,return_opt[,match_param[,subexpr]]]]])功能：返回满足正则表达式的字符串的起始位置或结束位置。
- 入参str是需要进行正则处理的字符串，支持STRING类型和NUMBER类型。
- 入参pattern是进行匹配的正则表达式。
- 入参position是起始位置，表示从第几个字符开始正则表达式匹配（默认为1）。
- 入参occurrence表示标识第几个匹配组，默认为1。
- 入参return_opt表示返回模式，0表示返回起始位置，1表示返回结束位置。
- 入参match_param表示模式（'i'表示不区分大小写进行检索；'c'表示区分大小写进行检索。默认为'c'。）。
- 入参subexpr表示，对存在子表达式的pattern，函数返回匹配第subexpr个子表达式的字符串（默认是0）。
- 函数返回值是INTEGER类型。
说明：当前不支持对CLOB和BLOB数据类型的处理。
返回字符串“17,20,23”中满足正则表达式“[^,]+”的字符串的起始位置，起始位置是第1个字符，标志第3个匹配组，不区分大小写进行检索。SELECT REGEXP_INSTR('17,20,23','[^,]+',1,3,0,'i') AS STR FROM SYS_DUMMY; 1 rows fetched.返回字符串“17,20,23”中满足正则表达式“[^,]+”的字符串的结束位置，起始位置是第1个字符，标志第3个匹配组，不区分大小写进行检索。
SELECT REGEXP_INSTR('17,20,23','[^,]+',1,3,1,'i') AS STR FROM SYS_DUMMY; 1 rows fetched.REGEXP_SUBSTR
语法：
REGEXP_SUBSTR(str,pattern[,position[,occurrence[,match_param[,subexpr]]]])功能：返回满足正则表达式的字符串。
- 入参str是需要进行正则处理的字符串，支持STRING类型和NUMBER类型。
- 入参pattern是进行匹配的正则表达式。
- 入参position是起始位置，表示从第几个字符开始正则表达式匹配（默认为1）。
- 入参occurrence表示标识第几个匹配组，默认为1。
- 入参match_param表示模式（'i'表示不区分大小写进行检索；'c'表示区分大小写进行检索。默认为'c'。）。
- 入参subexpr表示，对存在子表达式的pattern，函数返回匹配第subexpr个子表达式的字符串（默认是0）。
- 返回值是STRING。
当前不支持对CLOB和BLOB数据类型的处理。
返回字符串“17,20,23”中满足正则表达式“[^,]+”的字符串，起始位置是第1个字符，标志第3个匹配组，不区分大小写进行检索。SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM SYS_DUMMY; 1 rows fetched.REPLACE
语法：
REPLACE(str,src,dst)功能：将字符串str中对应的src子串替换为dst子串。
入参str表示原始字符串，入参src表示待替换的字符串，入参dst表示替换字符串；返回值是STRING。
- 当前不支持对CLOB数据和BLOB数据的处理。
- 返回值最大支持8000字节，超过则报错。
将字符串“fgsgswsgs”中的“sg”替换为“eeerrrttt”。SELECT REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') FROM SYS_DUMMY; REPLACE('FGSGSWSGS', 'SG' ,'EEERRRTTT') --------------------------------------- fgeeerrrtttsweeerrrttts 1 rows fetched.