oracle中执行字符串sql语句,Oracle SQL 字符串操作

这些函数全都接收的是字符族类型的参数(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;

return 1;

EXCEPTION

WHEN OTHERS THEN

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;

RETURN flag;

EXCEPTION

WHEN OTHERS THEN

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

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就可以解决

--调用: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 ;

--字符串的合并

--进行测试:select

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;

评论这张

cce544e7261bf3a4535656c82b969b03.png

转发至微博

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值