Spilt string SQL logic to create function

The below is the function where you need to write SQL query in DB2 to split the big string into parts. In this user defined function the following aggregate functions are used:

  • LENGTH
  • DECODE
  • SUBSTR
  • INSTR

Result of the function: it takes input as a string and splits it into small string based on split criteria (in this case’/’) and writes all the splitted strings as rows in the output.

How to write user defined function to split string.

     CREATE OR REPLACE FUNCTION PK_BASE.SPLIT(text VARCHAR(32000),
                                        split VARCHAR(10))
     RETURNS TABLE(column_values VARCHAR(60))
     RETURN WITH rec(rn, column_value, pos) AS
             (VALUES (1, VARCHAR(SUBSTR(text,1,DECODE(INSTR(text, split, 1), 0, LENGTH(text), 
                                 INSTR(text, split, 1) - 1)), 255),  
                                    INSTR(text, split, 1) + LENGTH(split))
              UNION ALL
              SELECT rn + 1, VARCHAR(SUBSTR(text, pos, DECODE(INSTR(text, split, pos), 
                                      0, 
                                      LENGTH(text) - pos + 1,  
                                      INSTR(text, split, pos) - pos)), 
                                      255),  
                             INSTR(text, split, pos) + LENGTH(split)
              FROM rec 
              WHERE rn < 30000 AND pos > LENGTH(split))
              SELECT column_value FROM rec;

    Let me explain how the above function is designed here. In the above example the function name is PK_BASE.SPLIT. Defined two variables. One is ‘text’ and the other one is ‘split’. The RETURNS TABLE clause to define data type for output. The output column name is ‘column_values‘. The ‘RETURN WITH rec’ is a sub query where will get three column values. Those are ‘rn’,’column_value’,’pos’. Here ‘rec’ is a temporary table.

    Example SQL query and the result.

    SELECT *

    FROM TABLE(PK_BASE.SPLIT(‘123/45/6/789/abc/def/ghi’, ‘/’));

    COLUMN_VALUES
     -------------------------
     123
     45
     6
     789
     abc
     def
     ghi
    10 record(s) selected.
    Advertisements

    Author: Srini

    Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

    Start Discussion

    Please log in using one of these methods to post your comment:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s