Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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 function the following functions are used:

  • LENGTH
  • DECODE
  • SUBSTR
  • INSTR

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 LENGTH(split))
      SELECT column_value FROM rec;

The following is an example 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.


Filed under: DB2 Tagged: DB2 Tips and Solutions, Split string function


This post first appeared on Srinimf - Tech.Jobs.Biz.Success, please read the originial post: here

Share the post

Spilt string SQL logic to create function

×

Subscribe to Srinimf - Tech.jobs.biz.success

Get updates delivered right to your inbox!

Thank you for your subscription

×