常用的字符串函数：

常用的字符串函数：

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;