Oralce字串函數！

常用的字串函數：

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;