SQL to split a string using user defined function.
Steps
- How to create Function
- How to call from COBOL program
First we have written function and the same function used in COBOL program.
The below UDF example you can use to Split the String supplied in input.
In the below 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 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.
