SQL: How to Create User-defined Function Quickly

SQL UDF to split a string how to create it explained. You will learn two things. Once is how to create and how to call it from COBOL program.

You will Learn.

  • How to create Function
  • How to call from COBOL program

First we have written function and the same called from COBOL program. Below UDF example you can use to Split the String. Below aggregate functions used to create UDF.

Aggregate Functions You Need.

  1. LENGTH
  2. DECODE
  3. SUBSTR
  4. 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 split strings as rows in the output.

How to Split a String Using UDF.

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. 

Author: Srini

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