Oracle split string, string split function.

First: Oracle string segmentation and Extraction

Split

Create or replace function get_strarraylength (av_str varchar2, -- the string av_split varchar2 -- separator) return numberis lv_str varchar2 (1000); lv_length number; begin lv_str: = ltrim (rtrim (av_str); lv_length: = 0; while instr (lv_str, av_split) <> 0 loop lv_length: = lv_length + 1; lv_str: = substr (lv_str, instr (lv_str, av_split) + Length (av_split), length (lv_str); End loop; lv_length: = lv_length + 1; return lv_length; end get_strarraylength;

 

Extract

 

Create or replace function get_strarraystrofindex (av_str varchar2, -- the string to be split av_split varchar2, -- separator av_index number -- Take the nth element) return varchar2is lv_str varchar2 (1024 ); lv_strofindex varchar2 (1024); lv_length number; begin lv_str: = ltrim (rtrim (av_str); lv_str: = Concat (lv_str, av_split); lv_length: = av_index; if lv_length = 0 then lv_strofindex: = substr (lv_str, 1, instr (lv_str, av_split)-length (av_split); else lv_length: = av_index + 1; lv_strofindex: = substr (lv_str, instr (lv_str, av_split, 1, av_index) + Length (av_split), instr (lv_str, av_split, 1, lv_length)-instr (lv_str, av_split, 1, av_index)-length (av_split); end if; return lv_strofindex; end get_strarraystrofindex;

Test: Select get_strarraystrofindex ('songguojun $ @ 000000', '$', 0) from dual

Result:

 

Second:

This function can split the "target string" with "specified string" and return results through the table structure. The Code is as follows:

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)    RETURN str_split     PIPELINEDAS    v_length   NUMBER := LENGTH(p_string);    v_start    NUMBER := 1;    v_index    NUMBER;BEGIN    WHILE(v_start <= v_length)    LOOP        v_index := INSTR(p_string, p_delimiter, v_start);        IF v_index = 0        THEN            PIPE ROW(SUBSTR(p_string, v_start));            v_start := v_length + 1;        ELSE            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));            v_start := v_index + 1;        END IF;    END LOOP;    RETURN;END splitstr;

After the creation, let's test it. For example, execute the following SQL:

select * from table(splitstr('Hello,Cnblogs!',','));

The output result is a two-row table, for example:

Convert rows into columns for display:

select a.column_value v1,b.column_value v2 from (select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) a,(select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) bwhere a.rn=1 and b.rn=2

This function can split the "target string" with "specified string" and return results through the table structure. The Code is as follows:

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)    RETURN str_split     PIPELINEDAS    v_length   NUMBER := LENGTH(p_string);    v_start    NUMBER := 1;    v_index    NUMBER;BEGIN    WHILE(v_start <= v_length)    LOOP        v_index := INSTR(p_string, p_delimiter, v_start);        IF v_index = 0        THEN            PIPE ROW(SUBSTR(p_string, v_start));            v_start := v_length + 1;        ELSE            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));            v_start := v_index + 1;        END IF;    END LOOP;    RETURN;END splitstr;

After the creation, let's test it. For example, execute the following SQL:

select * from table(splitstr('Hello,Cnblogs!',','));

The output result is a two-row table, for example:

Convert rows into columns for display:

select a.column_value v1,b.column_value v2 from (select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) a,(select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) bwhere a.rn=1 and b.rn=2

After the creation, let's test it. For example, execute the following SQL:

select * from table(splitstr('Hello,Cnblogs!',','));

The output result is a two-row table, for example:

Convert rows into columns for display:

select a.column_value v1,b.column_value v2 from (select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) a,(select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) bwhere a.rn=1 and b.rn=2

select * from table(splitstr('Hello,Cnblogs!',','));

The output result is a two-row table, for example:

Convert rows into columns for display:

select a.column_value v1,b.column_value v2 from (select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) a,(select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) bwhere a.rn=1 and b.rn=2

The output result is a two-row table, for example:

Convert rows into columns for display:

select a.column_value v1,b.column_value v2 from (select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) a,(select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) bwhere a.rn=1 and b.rn=2

Convert rows into columns for display:

select a.column_value v1,b.column_value v2 from (select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) a,(select * from (select rownum rn,t.* from table(splitstr('Hello,Cnblogs!',',')) t)) bwhere a.rn=1 and b.rn=2