[入门介绍] Gauss 100 数据库函数介绍之字符处理函数Part2

LTRIM(str)

语法:


LTRIM(str[,set])

功能:删除字符串左侧的空格或其他预定义字符。可使用此函数格式化查询的输出。


该函数从str的左端删除set中出现的所有字符,如果未指定set,则默认为删除空格。如果str是字符型数据,则必须将其括在单引号中。 LTRIM函数会查找str最左端的字符是否包含在set中,如果包含则删掉该字符,直到str最左端的字符不包含在set中时停止搜索。


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


说明:


当前不支持对CLOB数据和BLOB数据的处理。


示例:


从字符串“<=====>BROWNING <=====>”的最左侧删除小于号(<)、大于号(>)和等于号(=)。


SELECT LTRIM('<=====>BROWNING   <=====>', '<>=') "LTRIM Example" FROM DUAL;


LTRIM Example

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

BROWNING   <=====>


1 rows fetched.

不指定参数set,因此从字符串“ BROWNING”的最左侧删除空格。


SELECT LTRIM('   BROWNING') "LTRIM Example" FROM DUAL;


LTRIM Example

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

BROWNING


1 rows fetched.

从字符串“ LMN BROWNING”的最左侧删除字母“L”和字母“M”。


SELECT LTRIM(' LMN   BROWNING', 'LM') "LTRIM Example" FROM DUAL;


LTRIM Example

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

 LMN   BROWNING


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数据类型的处理。


示例1:


返回字符串“17,20,23”中满足正则表达式“[^,]+”的字符串的起始位置,起始位置是第1个字符,标志第3个匹配组,不区分大小写进行检索。

SELECT REGEXP_INSTR('17,20,23','[^,]+',1,3,0,'i') AS STR FROM DUAL;


STR

---

7


1 rows fetched.

示例2:


返回字符串“17,20,23”中满足正则表达式“[^,]+”的字符串的结束位置,起始位置是第1个字符,标志第3个匹配组,不区分大小写进行检索。


SELECT REGEXP_INSTR('17,20,23','[^,]+',1,3,1,'i') AS STR FROM DUAL;


STR

---

9


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 DUAL;


STR

---

23


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 DUAL;


REPLACE('FGSGSWSGS', 'SG' ,'EEERRRTTT')

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

fgeeerrrtttsweeerrrttts


1 rows fetched.

REVERSE

语法:


REVERSE(str)

功能:返回字符串的倒序。


仅支持STRING类型。


示例:


返回字符串“ABCD”的倒序。

SELECT REVERSE('ABCD') AS STR FROM DUAL;


STR

---

DCBA


1 rows fetched.

RIGHT

语法:


RIGHT(str,length)

功能:返回指定字符串的右边几位字符。


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


length是一个正整数,指定将从右边返回的字符个数。


说明:


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


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


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


示例:


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

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


RIGHT('ABCDEFG', 3)

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

efg


1 rows fetched.

RPAD

语法:


RPAD(str,pad_len[,pad_str])

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


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


说明:


返回值最大支持8000字节。


示例:


拼接输出first_name和last_name,并使用空格对first_name和last_name进行右侧填充至12个字符。

--删除表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;

--拼接输出first_name和last_name,并使用空格对first_name和last_name进行右侧填充至12个字符。

SELECT CONCAT(RPAD (first_name,12), RPAD (last_name,12)) FROM employee_2017 ORDER BY first_name, last_name;


CONCAT(RPAD (FIRST_NAME,12), RPAD (LAST_NAME,12))

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

ALAN        Jones

Alice       BROWN

BOB         Smith


3 rows fetched.

RTRIM

语法:


RTRIM(str[,set])

功能:删除字符串右侧的空格或其他预定义字符。可使用此函数格式化查询的输出。


该函数从str的右端删除set中出现的所有字符,如果未指定set,则默认为删除空格。如果str是字符型数据,则必须将其括在单引号中。 RTRIM函数会查找str最右端的字符是否包含在set中,如果包含则删掉该字符,直到str最右端的字符不包含在set中时停止搜索。


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


说明:


当前不支持对CLOB数据和BLOB数据的处理。


示例:


从字符串“<=====>BROWNING<=====>”的最右侧删除小于号(<)、大于号(>)和等于号(=)。


SELECT RTRIM('<=====>BROWNING<=====>', '<>=') "RTRIM Example" FROM DUAL;


RTRIM Example

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

<=====>BROWNING


1 rows fetched.

不指定参数set,因此从字符串“ BROWNING ”的最右侧删除空格。


SELECT RTRIM('   BROWNING   ') "RTRIM Example" FROM DUAL;


RTRIM Example

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

   BROWNING


1 rows fetched.

从字符串“ LMN BROWNINGER”的最右侧删除字母“E”和字母“R”。


SELECT RTRIM(' LMN   BROWNINGER', 'ER') "RTRIM Example" FROM DUAL;


RTRIM Example

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

 LMN   BROWNING


1 rows fetched.

SPACE

语法:


SPACE(n)

功能:生成n个空格。n取值范围[0,4000]


示例:


在字符串之间添加一个空格。


SELECT CONCAT('total number:',SPACE(1),'59');


CONCAT('TOTAL NUMBER:',SPACE(1),'59')

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

total number: 59


1 rows fetched.

SUBSTR

语法:


SUBSTR(str, start[,len])

SUBSTR(str FROM start [FOR len])

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


参数start表示索引位置,正值表示从左向右第|start|个字符位置,负值表示从右向左第|start|个字符位置。参数start取值为0时,索引位置为从左向右的第一个字符。参数len表示自索引位置开始向右截取的字符数,len≤0时,函数SUBSTR的返回值为空。len是可选参数,如果省略,则返回自索引位置开始到str末尾的所有字符。


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


示例:


从左向右第5个字符开始截取字符串,截取的字符串长度为6。


SELECT SUBSTR('Quadratically',5,6) EXMAPLE1, SUBSTR('Quadratically' FROM 5 FOR 6) EXAMPLE2 FROM DUAL;


EXMAPLE1 EXAMPLE2

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

ratica   ratica


1 rows fetched.

从左向右第5个字符开始截取字符串,截取的字符串长度为0,返回值为空。


SELECT SUBSTR('Quadratically',5,0) EXMAPLE1, SUBSTR('Quadratically' FROM 5 FOR 0) EXAMPLE2 FROM DUAL;


EXMAPLE1 EXAMPLE2

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



1 rows fetched.

从右向左第5个字符开始截取字符串,参数len省略,返回自索引位置开始到str末尾的所有字符。


SELECT SUBSTR('Quadratically',-5) EXMAPLE1, SUBSTR('Quadratically' FROM -5) EXAMPLE2 FROM DUAL;


EXMAPLE1 EXAMPLE2

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

cally    cally


1 rows fetched.

SUBSTRB

语法:


SUBSTRB(str, start[,len])

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


参数start表示索引位置,正值表示从左向右第|start|个字节位置,负值表示从右向左第|start|个字节位置。参数start取值为0时,索引位置为从左向右的第一个字节。参数len表示自索引位置开始向右截取的字节数,len≤0时,函数SUBSTR的返回值为空。len是可选参数,如果省略,则返回自索引位置开始到str末尾的所有字节。


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


示例:


从左向右第5个字节开始截取字符串,截取的字节数为6。


SELECT SUBSTRB('Quadratically',5,6) EXMAPLE FROM DUAL;


EXMAPLE

--------

ratica


1 rows fetched.

从左向右第5个字节开始截取字符串,截取的字节数为0,返回值为空。


SELECT SUBSTRB('Quadratically',5,0) EXMAPLE FROM DUAL;


EXMAPLE

-------



1 rows fetched.

从右向左第5个字节开始截取字符串,参数len省略,返回自索引位置开始到str末尾的所有字节。


SELECT SUBSTRB('Quadratically',-5) EXMAPLE FROM DUAL;


EXMAPLE

-------

cally


1 rows fetched.

SUBSTRING

语法:


SUBSTRING(str, start[,len])

SUBSTRING(str FROM start [FOR len])

功能:字符串截取函数。功能与SUBSTR相同,截取并返回str中从|start|开始的len个字符的子串。


参数start表示索引位置,正值表示从左向右第|start|个字符位置,负值表示从右向左第|start|个字符位置。参数start取值为0时,索引位置为从左向右的第一个字符。参数len表示自索引位置开始向右截取的字符数,len≤0时,函数SUBSTR的返回值为空。len是可选参数,如果省略,则返回自索引位置开始到str末尾的所有字符。


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


示例:


从左向右第5个字符开始截取字符串,截取的字符串长度为6。


SELECT SUBSTRING('Quadratically',5,6) EXMAPLE1, SUBSTRING('Quadratically' FROM 5 FOR 6) EXAMPLE2 FROM DUAL;


EXMAPLE1 EXAMPLE2

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

ratica   ratica


1 rows fetched.

从左向右第5个字符开始截取字符串,截取的字符串长度为0,返回值为空。


SELECT SUBSTRING('Quadratically',5,0) EXMAPLE1, SUBSTRING('Quadratically' FROM 5 FOR 0) EXAMPLE2 FROM DUAL;


EXMAPLE1 EXAMPLE2

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



1 rows fetched.

从右向左第5个字符开始截取字符串,参数len省略,返回自索引位置开始到str末尾的所有字符。


SELECT SUBSTRING('Quadratically',-5) EXMAPLE1, SUBSTRING('Quadratically' FROM -5) EXAMPLE2 FROM DUAL;


EXMAPLE1 EXAMPLE2

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

cally    cally


1 rows fetched.

SUBSTRING_INDEX

语法:


SUBSTRING_INDEX(str,delim,count)

功能:字符串截取函数。


count为正数时,返回第count个delim之前的所有字符;count为负数,返回倒数第|count|个delim后面的所有字符;当count为0时,返回值为空。


入参str、delim必须是可转成STRING的表达式,长度不超过8000。


入参count 必须是可转成NUMBER的表达式,取值范围[-2147483648,2147483647]。


示例:


获取IP地址第一个数字。


SELECT SUBSTRING_INDEX('192.168.0.1','.',1);


SUBSTRING_INDEX('192.168.0.1','.',1)

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

192


1 rows fetched.

TO_NCHAR

语法:


TO_NCHAR(text_exp)TO_NCHAR(datetime_exp, [datetime_fmt])

功能:将文本表达式、日期表达式、数字等转换为NCHAR格式,可用于格式化输出结果。


示例:


将日期按照指定格式转为NCHAR输出。


SELECT TO_NCHAR(TO_DATE('2018-06-06 10:18:36', 'yyyy-mm-dd hh24:mi:ss'), 'mm-yyyy-dd') FROM DUAL;


TO_NCHAR(TO_DATE('2018-06-06 10:18:36', 'YYYY-MM-DD HH24:MI:SS'))

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

06-2018-06

1 rows fetched.

TRIM

语法:


TRIM ( [ { LEADING | TRAILING | BOTH } ] [ set ] FROM str )

功能:按指定的方向从传入的字符串中删除空格或其他预定义字符。可使用此函数格式化查询的输出。


TRIM方向可以有三种:LEADING表示从字符串首开始删除;TRAILING表示从字符串尾开始删除;BOTH表示从两端都进行删除。若LEADING、TRAILING、BOTH都未指定,则默认等同于从两端都进行删除。


参数set指示的是一个字符集合。只要str的开头或结尾包含这个字符集合中的任一字符,都满足trim的条件,会被执行trim操作。如果未指定set,则默认为删除空格。


入参str为可转换成STRING的表达式,入参set为SQL语法的字符;返回值是STRING。


说明:


该函数也可以用普通函数的参数形式调用,调用方法是TRIM( str [, set]),使用这种方法调用时,无法指定TRIM方向,默认从两端都进行删除。如果未指定set,则默认为从str的两端删除空格。


当前不支持对CLOB数据和BLOB数据的处理。


示例:


从字符串“123sfd111”的两侧删除数字1。

SELECT TRIM(BOTH '1' FROM '123sfd111') FROM DUAL;


TRIM(BOTH '1' FROM '123SFD111')

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

23sfd


1 rows fetched.

UPPER

语法:


UPPER(str)

功能:将字符串转换成对应字符的大写。


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


示例:


将字符串“mr. scott mcmillan”的所有字符转换为大写字符。

SELECT UPPER('mr. scott mcmillan') "Uppercase" FROM DUAL;


Uppercase

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

MR. SCOTT MCMILLAN


1 rows fetched.