Oracle SQL 字符串操作
来源：互联网 发布：db2查看正在执行的sql 编辑：程序博客网 时间：2024/05/16 00:43
这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.
除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.
字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。
字符型变量存储的最大值：
VARCHAR2数值被限制为2000字符(ORACLE 8中为4000字符)
CHAR数值被限制为255字符(在ORACLE8中是2000)
long类型为2GB
Clob类型为4GB
1、CHR
语法: chr(x)
功能：返回在数据库字符集中与X拥有等价数值的字符。CHR和ASCII是一对反函数。经过CHR转换后的字符再经过ASCII转换又得到了原来的字
符。
使用位置：过程性语句和SQL语句。
2、CONCAT
语法： CONCAT（string1,string2）
功能：返回string1，并且在后面连接string2。
使用位置：过程性语句和SQL语句。
3、INITCAP
语法：INITCAP（string）
功能：返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string。单词是用.空格或给字母数字字符进行分隔。不是字母的
字符不变动。
使用位置：过程性语句和SQL语句。
4、LTRIM
语法：LTRIM（string1,string2）
功能：返回删除从左边算起出现在string2中的字符的string1。String2被缺省设置为单个的空格。数据库将扫描string1，从最左边开始。当
遇到不在string2中的第一个字符，结果就被返回了。LTRIM的行为方式与RTRIM很相似。
使用位置：过程性语句和SQL语句。
5、NLS_INITCAP
语法：NLS_INITCAP（string[,nlsparams]）
功能：返回字符串每个单词第一个字母大写而单词中的其他字母小写的string，nlsparams
指定了不同于该会话缺省值的不同排序序列。如果不指定参数，则功能和INITCAP相同。Nlsparams可以使用的形式是：
'NLS_SORT=sort'
这里sort制订了一个语言排序序列。
使用位置：过程性语句和SQL语句。
6、NLS_LOWER
语法：NLS_LOWER（string[,nlsparams]）
功能：返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。
Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。如果nlsparams没有被包含，那么NLS_LOWER所作的处理和
LOWER相同。
使用位置；过程性语句和SQL语句。
7、NLS_UPPER
语法：nls_upper（string[,nlsparams]）
功能：返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLS_INITCAP中的相同。如果
没有设定参数，则NLS_UPPER功能和UPPER相同。
使用位置：过程性语句和SQL语句。
8、REPLACE
语法：REPLACE（string，search_str[,replace_str]）
功能：把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串
search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。
使用位置：过程性语句和SQL语句。
9、RPAD
语法：RPAD（string1,x[,string2]）
功能：返回在X字符长度的位置上插入一个string2中的字符的string1。如果string2的长度要比X字符少，就按照需要进行复制。如果string2
多于X字符，则仅string1前面的X各字符被使用。如果没有指定string2，那么使用空格进行填充。X是使用显示长度可以比字符串的实际长度
要长。RPAD的行为方式与LPAD很相似，除了它是在右边而不是在左边进行填充。
使用位置：过程性语句和SQL语句。
10、RTRIM
语法: RTRIM（string1,[,string2]）
功能: 返回删除从右边算起出现在string1中出现的字符string2. string2被缺省设置为单个的空格.数据库将扫描string1,从右边开始.当遇
到不在string2中的第一个字符,结果就被返回了RTRIM的行为方式与LTRIM很相似.
使用位置：过程性语句和SQL语句。
11、SOUNDEX
语法: SOUNDEX（string）
功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助.
使用位置：过程性语句和SQL语句。
12、SUBSTR
语法: SUBSTR（string,a[,b]）
功能: 返回从字母为值a开始b个字符长的string的一个子字符串.如果a是0,那么它就被认为从第一个字符开始.如果是正数,返回字符是从左
边向右边进行计算的.如果b是负数,那么返回的字符是从string的末尾开始从右向左进行计算的.如果b不存在,那么它将缺省的设置为整个字符
串.如果b小于1,那么将返回NULL.如果a或b使用了浮点数,那么该数值将在处理进行以前首先被却为一个整数.
使用位置：过程性语句和SQL语句。
13、TRANSLATE
语法: TRANSLATE(string,from_str,to_str)
功能: 返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的string. TRANSLATE是REPLACE所提供的功能的一个超集.
如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符. to_str不能为空
.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.
使用位置：过程性语句和SQL语句。
14、UPPER
语法: UPPER（string）
功能: 返回大写的string.不是字母的字符不变.如果string是CHAR数据类型的,那么结果也是CHAR类型的.如果string是VARCHAR2类型的,那么
结果也是VARCHAR2类型的.
使用位置: 过程性语句和SQL语句。
字符函数——返回数字
这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许多结果都是整数值,但是返回结果都是简单的NUMBER
类型的,没有定义任何的精度或刻度范围.
16、ASCII
语法: ASCII（string）
功能: 数据库字符集返回string的第一个字节的十进制表示.请注意该函数仍然称作为ASCII.尽管许多字符集不是7位ASCII.CHR和ASCII是互为
相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.
使用位置: 过程性语句和SQL语句。
17、INSTR
语法: INSTR（string1, string2[a,b]）
功能: 得到在string1中包含string2的位置. string1时从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行
扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有
找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.
使用位置: 过程性语句和SQL语句。
18、INSTRB
语法: INSTRB（string1, string2[a,[b]]）
功能: 和INSTR相同,只是操作的对参数字符使用的位置的是字节.
使用位置: 过程性语句和SQL语句。
19、LENGTH
语法: LENGTH（string）
功能: 返回string的字节单位的长度.CHAR数值是填充空格类型的,如果string由数据类型CHAR,它的结尾的空格都被计算到字符串长度中间.
如果string是NULL,返回结果是NULL,而不是0.
使用位置: 过程性语句和SQL语句。
20、LENGTHB
语法: LENGTHB（string）
功能: 返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样的.
使用位置: 过程性语句和SQL语句。
21、NLSSORT
语法: NLSSORT（string[,nlsparams]）
功能: 得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了一致性. Nlsparams的作用和
NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.
使用位置: 过程性语句和SQL语句。
select substr(t.cm_node,0,instr(t.cm_node,'/',-1)) from cm_node t;
CREATE OR REPLACE PACKAGE PKG_STR_CONV AS
/*
此函数包定义了大多数的字符串的操作的功能
*/
--判断输入的字符串是否全部都是数字
Function isnumber(str varchar2 ) RETURN NUMBER;
--判断输入的字符串是否全部是汉字
Function ischinese(str varchar2 ) RETURN VARCHAR2;
--保留字符串中全部数字
FUNCTION get_number(theStr IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
--去掉字符串中的开头和结尾的非数字字符
FUNCTION start_end_number(theStr IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
--将字符串中所有的非汉字字符去掉
FUNCTION trim_chars (theStr IN VARCHAR2) RETURN VARCHAR2;
--取出字符串中第一个汉字的位置
FUNCTION count_chars (theStr IN VARCHAR2) RETURN VARCHAR2;
--统计子字符串在主字符串中出现的频率
FUNCTION freq_instr1(string_in IN VARCHAR2,substring_in IN VARCHAR2,match_case_in IN VARCHAR2 :='IGNORE') RETURN NUMBER;
--从第一个字符串中去除第二个字符串中的字符
Function cutstr(p_FirstStr VARCHAR2,p_SecondStr VARCHAR2) RETURN VARCHAR2;
--把第一个字符串按第二个字符串中字符的顺序排列的函数
FUNCTION sortstr (p_Str VARCHAR2,p_SecondStr VARCHAR2) RETURN VARCHAR2;
--拆分字符串,插入测试表的的存储过程
PROCEDURE insert_demo(p_INSERT_STR VARCHAR2);
--合并字符串的函数
function str_list2(key_name in varchar2,key in varchar2,coname in varchar2,tname IN varchar2 ) return VARCHAR2;
--删除字符串中的重复字符
function delre(p_str varchar2, p_token char := ',') return VARCHAR2;
--字段名和值均以字符串形式保存到第三方表的数据集
--函数递归执行实现表达式
END pkg_str_conv;
/
CREATE OR REPLACE PACKAGE BODY PKG_STR_CONV AS
--判断输入的字符串是否全部都是数字
Function isnumber(str varchar2 )
RETURN NUMBER
IS
n NUMBER;
BEGIN
SELECT to_number(str) INTO n FROM dual;/*判断是数字的情况返回1*/
return 1;
EXCEPTION
WHEN OTHERS THEN/*是非数字的情况返回0*/
return 0;
END;
--判断输入的字符串是否全部都是汉字
Function ischinese(str varchar2 )
RETURN VARCHAR2
IS
flag VARCHAR2(5);
BEGIN
SELECT decode(lengthb(str),2*length(str),'true','false') INTO flag from dual;/*判断是汉字的情况返回1*/
RETURN flag;
EXCEPTION
WHEN OTHERS THEN/*是非数字的情况返回0*/
RETURN NULL;
END;
--保留字符串中全部数字
FUNCTION get_number(theStr IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
AS
CURR_CHAR VARCHAR2(3);
rtVal VARCHAR2(4000);
BEGIN
rtVal:=NULL;
IF theStr IS NOT NULL THEN
FOR i IN 1..LENGTH(theStr) LOOP
CURR_CHAR:=SUBSTR(theStr,i,1);
--ASCII(0..9)=48..57/*ascii码在48到57的是数字0到9*/
IF ASCII(CURR_CHAR) BETWEEN 48 AND 57 THEN
rtVal:=rtVal || CURR_CHAR;
END IF;
END LOOP;
END IF;
RETURN rtVal;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END;
--去掉字符串中的开头和结尾的非数字字符
FUNCTION start_end_number(theStr IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 AS
CURR_CHAR VARCHAR2(2);
rtVal VARCHAR2(4000);
BEGIN
rtVal := LTRIM(RTRIM(thestr));/*将输入字符串的左右空格去掉*/
IF rtVal IS NOT NULL THEN/*去掉字符串的前面的非数字字符*/
FOR i IN 1 .. LENGTH(rtVal) LOOP
CURR_CHAR := SUBSTR(rtVal, 1, 1);
IF ISNUMBER(CURR_CHAR) = 0 THEN
rtVal := SUBSTR(rtVal, 2);
ELSE
FOR i IN 1 .. LENGTH(rtVal) LOOP/*去掉字符串后面的非数字字符*/
CURR_CHAR := SUBSTR(rtVal, -1, 1);
IF ISNUMBER(CURR_CHAR) = 0 THEN
rtVal := SUBSTR(rtVal, 1, LENGTH(rtVal) - 1);
ELSE
RETURN rtVal;
END IF;
END LOOP;
END IF;
END LOOP;
END IF;
RETURN rtVal;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END START_END_NUMBER;
--将字符串中所有的非汉字字符去掉
FUNCTION trim_chars (theStr IN VARCHAR2) RETURN VARCHAR2
IS
rtVal VARCHAR2(4000);
CURR_CHAR VARCHAR2(2);
STR VARCHAR2(4000);
BEGIN
rtVal:='';
STR:=theStr;
IF STR IS NOT NULL THEN
FOR i IN 1..LENGTH(STR) LOOP
CURR_CHAR:=SUBSTR(STR,i,1);
IF LENGTHB(CURR_CHAR)=2 THEN
rtVal:=rtVal || CURR_CHAR;
END IF;
END LOOP;
END IF;
RETURN rtVal;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END;
--取出字符串中第一个汉字的位置
FUNCTION count_chars (theStr IN VARCHAR2) RETURN VARCHAR2
IS
count number(38);
CURR_CHAR VARCHAR2(2);
STR VARCHAR2(4000);
BEGIN
STR:=rtrim(ltrim(theStr));
IF STR IS NOT NULL THEN
FOR i IN 1..LENGTH(STR) LOOP
CURR_CHAR:=SUBSTR(STR,i,1);
IF LENGTHB(CURR_CHAR)=2 THEN
count:=i;
END IF;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END;
--统计子字符串在主字符串中出现的频率
FUNCTION freq_instr1(string_in IN VARCHAR2,substring_in IN VARCHAR2,match_case_in IN VARCHAR2 := 'IGNORE') RETURN NUMBER AS
search_loc NUMBER := 1;
substring_len NUMBER := LENGTH (substring_in);
check_again BOOLEAN := TRUE;
return_value NUMBER := 0;
BEGIN
IF string_in IS NOT NULL AND substring_in IS NOT NULL
THEN
WHILE check_again
LOOP
IF UPPER (match_case_in) = 'IGNORE'
THEN
search_loc :=
INSTR (UPPER (string_in),
UPPER (substring_in), search_loc, 1);
ELSE
search_loc := INSTR (string_in, substring_in, search_loc, 1);
END IF;
check_again := search_loc > 0;
IF check_again
THEN
return_value := return_value + 1;
search_loc := search_loc + substring_len;
END IF;
END LOOP;
END IF;
RETURN return_value;
END freq_instr1;
--函数功能:CutStr（从第一个字符串中去除第二个字符串中的字符的函数）
--传入参数:p_FirstStr-第一个字符串,p_SecondStr-第二个字符串
--功能说明:从第一个字符串中去除第二个字符串中的字符
--详细说明:循环取出第二个字符串中的字符，如果该字符在第一个字符串中存在，则从第一个字符串中去除该字符，并返回剩下部分字符串
FUNCTION cutstr(p_FirstStr IN VARCHAR2,p_SecondStr IN VARCHAR2) RETURN VARCHAR2
IS
l_StrLength INT;
l_CurrentChar VARCHAR2(2);
l_ReturnStr VARCHAR2(4000);
BEGIN
l_ReturnStr:=p_FirstStr;
l_StrLength:=length(p_SecondStr);
WHILE l_StrLength>0 LOOP
l_CurrentChar:=substr(p_SecondStr,l_StrLength,1);
l_ReturnStr:=replace(l_ReturnStr,l_CurrentChar,'');
l_StrLength:=l_StrLength-1;
END LOOP;
RETURN l_ReturnStr;
END;
--函数功能:SortStr（把第一个字符串按第二个字符串中字符的顺序排列的函数）
--传入参数:p_Str-第一个字符串, p_SecondStr-第二个字符串
--功能说明:把第一个字符串按第二个字符串中字符的顺序排列的函数
--详细说明:顺序取出第二个字符串中的在第一个字符串的字符组合，并返回该字符组合
FUNCTION sortstr ( p_Str VARCHAR2,p_SecondStr VARCHAR2) RETURN VARCHAR2
IS
l_StrLength INT;
l_ReturnStr VARCHAR2(4000):='';
l_CurrentChar VARCHAR2(2);
BEGIN
l_StrLength:=length(p_SecondStr);
WHILE l_StrLength>0 LOOP
l_CurrentChar:=substr(p_SecondStr,l_StrLength,1);
IF instr(p_Str,l_CurrentChar)>0 THEN
l_ReturnStr:=l_CurrentChar||l_ReturnStr;
END IF;
l_StrLength:=l_StrLength-1;
END LOOP;
RETURN l_ReturnStr;
END;
--字符串的拆分
PROCEDURE insert_demo(p_INSERT_STR VARCHAR2)
--简单的拆分sql就可以解决
/*
拆分一个以逗号分隔的字符串，然后在每个拆分出来的字符串上加上单引号。
'AAAA,BB,DDDDDD'->'AAAA','BB','DDDDDD'
select (''''||(replace('AAAA,BBBB,CCCC',',',''''||','||''''))||'''') from dual
*/
/*
--
CREATE TABLE CSDN_DEMO (ID CHAR(3),FIELD_1 VARCHAR2(10),FIELD_2 VARCHAR2(10));
参数说明：
p_INSERT_STR输入参数，列以"￥"分隔，行以"，"分隔的字符串，如 '001￥12￥13，002￥22￥23，003￥32￥33'
注意：默认情况，该参数长度小于 250 个字符，如需更改，请根据实际情况调整参数
*/
--调用:EXEC INSERT_DEMO('001￥12￥13，002￥22￥23，003￥32￥33');
--查询:SELECT * FROM CSDN_DEMO;
AS
v_STR_TEMP VARCHAR2(502);
v_STR_CURR VARCHAR2(500);
v_STR_INSERT_1 VARCHAR2(100) := 'INSERT INTO CSDN_DEMO (ID, FIELD_1, FIELD_2) VALUES ( ';
v_STR_INSERT_2 VARCHAR2(500);
BEGIN
IF p_INSERT_STR IS NULL THEN
DBMS_OUTPUT.PUT_LINE('输入参数为空！');
END IF;
v_STR_TEMP := SUBSTR(p_INSERT_STR,1,250) || '，';
LOOP
v_STR_CURR := SUBSTR(v_STR_TEMP,1,INSTR(v_STR_TEMP,'，')-1) || '￥';
v_STR_INSERT_2 := NULL;
LOOP
v_STR_INSERT_2 := v_STR_INSERT_2 || ''''||SUBSTR(v_STR_CURR,1,INSTR(v_STR_CURR,'￥')-1) || ''',';
v_STR_CURR := SUBSTR(v_STR_CURR,INSTR(v_STR_CURR,'￥')+1);
EXIT WHEN v_STR_CURR IS NULL;
END LOOP;
v_STR_INSERT_2 := SUBSTR(v_STR_INSERT_2,1,LENGTH(v_STR_INSERT_2)-1);
v_STR_INSERT_2 := v_STR_INSERT_2 || ')';
EXECUTE IMMEDIATE v_STR_INSERT_1 || v_STR_INSERT_2;
v_STR_TEMP := SUBSTR(v_STR_TEMP,INSTR(v_STR_TEMP,'，')+1);
EXIT WHEN v_STR_TEMP IS NULL;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR:'||SQLERRM,1,255));
END /*INSERT_DEMO*/;
--字符串的合并
--进行测试:select test.country
/*
str_list2('COUNTRY', test.country, 'CITY', 'TEST') emplist
from test
group by test.country
*/
function str_list2( key_name in varchar2,
key in varchar2,
coname in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(4000);
sep varchar2(2);
val varchar2(4000);
cur rc;
begin
open cur for 'select '||coname||'
from '|| tname || '
where ' || key_name || ' = :x '
using key;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ', ';
end loop;
close cur;
return str;
end;
--删除字符串中的重复字符:
--测试:select hashset('AA,AA,CC,BB,CC,AA,DD,CC,FF,CC',',') a,hashset('1,1,1,2,2,2,3,3,4,5,6,7,8,9,123,234,......') b from dual;
function delre(p_str varchar2, p_token char := ',')
return varchar2 as
type t_tab is table of int index by varchar2(60);
l_tab t_tab;
l_n pls_integer := 0;
l_vc varchar2(500) := p_str;
idx varchar2(60);
begin
if substr(l_vc, -1, 1) <> p_token then
l_vc := l_vc || p_token;
end if;
loop
l_n := instr(l_vc, p_token);
exit when(nvl(l_n, 0) = 0);
l_tab(ltrim(rtrim(substr(l_vc, 1, l_n - 1)))) := 0;
l_vc := substr(l_vc, l_n + 1);
end loop;
idx := l_tab.first;
while idx is not null loop
l_vc := l_vc || p_token || idx;
idx := l_tab.next(idx);
end loop;
return substr(l_vc, 2);
end;
END pkg_str_conv;
- Oracle SQL 字符串操作
- Oracle SQL 字符串操作
- Oracle 的字符串操作
- ORACLE 字符串操作
- ORACLE 字符串操作
- ORACLE 字符串操作
- ORACLE 字符串操作(转)
- oracle 字符串操作
- java /oracle字符串操作
- ORACLE 字符串操作函数
- ORACLE 字符串操作
- Oracle字符串操作
- Oracle字符串操作
- oracle 字符串操作
- sql字符串操作函数
- SQL字符串操作汇总
- SQL字符串操作汇总
- SQL字符串操作汇总
- 如何在中关村买数码产品
- cidaemon.exe进程
- 对于大日志包进行分割的N种方法
- 服务器配置
- android之init进程-uevent
- Oracle SQL 字符串操作
- Boost下载安装编译配置使用指南（含Windows和Linux）
- android kernel 对 标准kernel 的改动
- SQL Server中常用全局变量介绍
- linux下破解Python集成开发环境WingIDE4.1
- 常用汉字的UTF-8编码及编码范围
- 利用jSMSEngine开源包进行短信的收发
- JDK自带的native2ascii转码工具使用详解
- 谈谈C++中使用或和与来选择模式标志的实现
