常用的字符串函数：
SQL> select instr('Hello World!','o',1,1),instr('Hello World!','o',1,2),instr('Hello World!','o',6,2) from dual;
INSTR('HELLOWORLD!','O',1,1) INSTR('HELLOWORLD!','O',1,2) INSTR('HELLOWORLD!','O',6,2)
---------------------------- ---------------------------- ----------------------------
5 8 0
SQL> select substr('Hello World!',1,4),substr('Hello World!',3,4) from dual;
SUBSTR('HELLOWORLD!',1,4) SUBSTR('HELLOWORLD!',3,4)
------------------------- -------------------------
Hell llo
SQL> SELECT TRANSLATE('as f as f as bbdd', ' afb', '*?@') FROM DUAL;
TRANSLATE('ASFASFASBBDD','AFB'
------------------------------PS:每个字符匹配替换
?s*@*?s*@*?s*dd
SQL> SELECT REPLACE('as f af af as', 'af', 't') FROM DUAL;
REPLACE('ASFAFAFAS','AF','T')
-----------------------------PS:字符串匹配替换
as f t t as
SQL> select lpad('aaa',10,'#') from dual;
LPAD('AAA',10,'#')(默认为空格)
------------------
#######aaa
SQL> select rpad('aaa',10,'#') from dual;
RPAD('AAA',10,'#')(默认为空格)
------------------
aaa#######
ltrim(字符串1,字符串2) 和rtrim(字符串1,字符串2)
PS:从字符串1的左端开始扫描,逐一去掉在字符串2中出现的字符,当遇到不是字符串2中的字符终止,返回结果集.
SQL> select ltrim('morning','m'),ltrim('morning','or') from dual;
LTRIM('MORNING','M') LTRIM('MORNING','OR')
-------------------- ---------------------
orning morning
SQL> select rtrim('morning','m'),rtrim('morning','ng') from dual;
RTRIM('MORNING','M') RTRIM('MORNING','NG')
-------------------- ---------------------
morning morni
SQL> select trim('a' from 'abcaa') from dual;
TRIM('A'FROM'ABCAA')
--------------------PS:截取的字符集(蓝色部分)只能为一个字符
bc
SQL> select initcap('huang-kai-bin') from dual;
INITCAP('HUANG-KAI-BIN')
------------------------
Huang-Kai-Bin
SQL> select length('hello world') from dual;
LENGTH('HELLOWORLD')
--------------------
11
SQL> select upper('Hello World') from dual;
UPPER('HELLOWORLD')
-------------------
HELLO WORLD
SQL> select lower('Hello World') from dual;
LOWER('HELLOWORLD')
-------------------
hello world
SQL> select concat('Hello',' World') from dual;
CONCAT('HELLO','WORLD')
-----------------------ps:效果和字符串连接符 || 相同
Hello World
写了一个简单的过程，把一个分段记录插入表中：
在这个过程中，用到了两个字符串的函数：
1.SUBSTR(目标字符串，输出的子串的起点，子串的长度)
如：select substr(ssn,1,3)|| ','||substr(ssn,4,2)||','||substr(ssn,6,4);
输出: ssn ssn
300541117 变成 300-54-1117
2.INSTR(目标字符串，匹配的内容，搜索起点，第几个满足条件)
如：select lastname,instr(lastname,'o',2,1) from characters;
输出: lastname instr(lastname,'o',2,1)
purvis 0
kong 2
create or replace procedure p_hyj_hkb_test is
v_in_string varchar2(10000);
v_post number(4);
v_out_string varchar2(5000);
v_cn number(1);
create_table varchar2(500);
v_name varchar2(20);
v_age number(2);
v_address varchar2(100);
i number(2);
v_in_string2 varchar2(10000);
--v_post2 number(4);
begin
begin
select count(*)
into v_cn
from user_tables a
where a.table_name = 'USER_INFO';
if v_cn = 0 then
--表不存在,建立
create_table := 'create table user_info(name varchar2(15),
age number(6),
address varchar2(100))';
execute immediate create_table;
else
--表存在
null;
end if;
end;
begin
--把串'黄开彬,25,福建省福州市闽清,'写到一个表里，字段要按user_name,age,address这三个字段来保存
v_in_string := '黄开彬,25,福建省福州市闽清县;黄依江,30,福建省福州市;小黄,35,福建省垃圾的;';
while instr(v_in_string, ';', 1) > 0 loop
if v_in_string is null then
goto end_loop;
end if;
v_post := instr(v_in_string, ';', 1);
v_in_string2 := substr(v_in_string, 1, v_post - 1)||',';
v_in_string := substr(v_in_string, v_post + 1);
i := 1;
while instr(v_in_string2, ',', 1) > 0 loop
if v_in_string2 is null then
goto end_loop;
end if;
v_post := instr(v_in_string2, ',', 1);
v_out_string := substr(v_in_string2, 1, v_post - 1);
v_in_string2 := substr(v_in_string2, v_post + 1);
if i = 1 then
v_name := v_out_string;
elsif i = 2 then
v_age := v_out_string;
elsif i = 3 then
v_address := v_out_string;
end if;
i := i + 1;
end loop;
--插入表
begin
-- insert into user_info values (v_name, v_age, v_address);
create_table := 'insert into user_info values(' || '''' || v_name || '''' || ',' || '''' ||
v_age || '''' || ',' || '''' || v_address || '''' || ')';
execute immediate create_table;
end;
end loop;
end;
<<end_loop>>
null;
end p_hyj_hkb_test;
