Description of the DB2 split operation procedure

The following articles mainly describe the actual operation process for implementing split in DB2. If you are interested in the actual operation process for implementing split in DB2, you can use the following articles to get a better understanding of the specific solution and hope it will be helpful for your future study.

Ask you a question and use the function to implement the split function to split strings.

If the input string is 'a, B, C ',

The output result is line.

Row 2 B

Row 3 c

Evaluate the specific DB2 implementation functions.

The implementation method of oracle is as follows:

 
 

  
  
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);  
  
  
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)  
  
  
RETURN ty_str_split  
  
  
IS  
  
  
j INT := 0;  
  
  
i INT := 1;  
  
  
len INT := 0;  
  
  
len1 INT := 0;  
  
  
str VARCHAR2 (4000);  
  
  
str_split ty_str_split := ty_str_split ();  
  
  
BEGIN  
  
  
len := LENGTH (p_str);  
  
  
len1 := LENGTH (p_delimiter);  
  
  
WHILE j < len 
  
  
LOOP  
  
  
j := INSTR (p_str, p_delimiter, i);  
  
  
IF j = 0 
  
  
THEN  
  
  
j := len;  
  
  
str := SUBSTR (p_str, i);  
  
  
str_split.EXTEND;  
  
  
str_split (str_split.COUNT) := str;  
  
  
IF i >= len  
  
  
THEN  
  
  
EXIT;  
  
  
END IF;  
  
  
ELSE  
  
  
str := SUBSTR (p_str, i, j - i);  
  
  
i := j + len1;  
  
  
str_split.EXTEND;  
  
  
str_split (str_split.COUNT) := str;  
  
  
END IF;  
  
  
END LOOP;  
  
  
RETURN str_split;  
  
  
END fn_split;  
  
  
/  
 
 

Test: SELECT * from table (fn_split ('a, B, C ',','));

Result:

A

B

C

Best Answer leo

You can write a udf for segmentation, but I prefer SQL:

 
 

  
  
with n(str, ori, pos) as (  
  
  
values ('abc,bc,cd,d,ff,', 1, posstr('abc,bc,cd,d,ff,', ','))  
  
  
union all   
  
  
select str, pos+1, locate(',', str, pos+1)  
  
  
from n   
  
  
where locate(',', str, pos+1)>0)  
  
  
select str, ori, pos, substr(str, ori, pos-ori) as result from n   
 
 

You can write a udf for segmentation, but I prefer SQL:

 
 

  
  
with n(str, ori, pos) as (  
  
  
values ('abc,bc,cd,d,ff,', 1, posstr('abc,bc,cd,d,ff,', ','))  
  
  
union all   
  
  
select str, pos+1, locate(',', str, pos+1)  
  
  
from n   
  
  
where locate(',', str, pos+1)>0)  
  
  
select str, ori, pos, substr(str, ori, pos-ori) as result from n   
 
 

The above content is an introduction to the implementation of split in DB2. I hope you will have some gains.