9

I have a VARCHAR column that contains 5 informations (2 CHAR(3) and 3 TIMESTAMP) separated with '$'. 

CREATE TABLE MYTABLE (
  COL VARCHAR(256) NOT NULL
);

INSERT INTO MYTABLE 
VALUES
    ( 'AAA$000$2009-10-10 10:50:00$null$null$null' ),
    ( 'AAB$020$2007-04-10 10:50:00$null$null$null' ),
    ( 'AAC$780$null$2007-04-10 10:50:00$2009-04-10 10:50:00$null' )
;

I would like to extract the 4th field ...

'AAA$000$2009-10-10 10:50:00$null$null$null'
                             ^^^^ this field

... to have something like

SELECT SPLIT(COL, '$', 4) FROM MYTABLE

1
-----
'null'
'null'
'2009-04-10 10:50:00'

I'm searching, in that order :

  1. A DB2 build-in string function
  2. An embeddable statement such as SUBSTR(COL, POSSTR(COL)+1)...
  3. An user defined function that behaves like SPLIT

Precision : Yes, I do know that it's not a good idea to have such columns...

Steve Schnepp
  • 4,620
  • 5
  • 39
  • 54

5 Answers5

7
CREATE FUNCTION split(pos INT, delimeter CHAR, string VARCHAR(255))
LANGUAGE SQL
RETURNS VARCHAR(255)
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
    DECLARE x INT;
    DECLARE s INT;
    DECLARE e INT;

    SET x = 0;
    SET s = 0;
    SET e = 0;

    WHILE (x < pos) DO
        SET s = locate(delimeter, string, s + 1);
        IF s = 0 THEN
            RETURN NULL;
        END IF;
        SET x = x + 1;
    END WHILE;

    SET e = locate(delimeter, string, s + 1);
    IF s >= e THEN
        SET e = LENGTH(string) + 1;
    END IF;
    RETURN SUBSTR(string, s + 1, e - s -1);
END!

Usage:

SELECT split(3,'$',col) from mytable; -- or
SELECT split(0,'-', 'first-second-third') from sysibm.sysdummy1;
SELECT split(0,'-', 'returns this') from sysibm.sysdummy1;
SELECT split(1,'-', 'returns null') from sysibm.sysdummy1;
Toni
  • 338
  • 3
  • 10
  • Accepting it, since it's exactly what I searched (3rd option). Can you just add the `DETERMINISTIC` and the `NO EXTERNAL ACTION` modifier, to be able to use it in a `GROUP BY` clause ? – Steve Schnepp Apr 16 '12 at 16:01
  • Good point. Changed to return NULL when element not found. Requesting first element, with not-existing-deliemeter will return the original string. – Toni Apr 17 '12 at 06:46
4

I am sure there is a better way to write this, but here is 1 (SQL) solution for the simple case given. It could be rewritten as a stored procedure to look for any arbitrary string. There may also be some 3rd party tools/extensions to help out w/ the split you want...

select
locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1) as poss3rdDollarSign, -- position of 3rd dollar sign
locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1) as poss4thDollarSign, -- position of 4th dollar sign
    (locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1)) - 
    (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) - 1  as stringLength,-- length of string between 3rd and 4th dollar sign
    substr(col, locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)  + 1, (locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1)) - 
    (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) - 1) as string
    from mytable
nycjay
  • 51
  • 5
1

try this, it works!

CREATE FUNCTION SPLIT( P_1 VARCHAR(3200),
                       P_2 VARCHAR(200))
    RETURNS TABLE(P_LIST VARCHAR(3200))
    SPECIFIC SPLIT
    LANGUAGE SQL
    MODIFIES SQL DATA
    NO EXTERNAL ACTION
F1: BEGIN
    return
    with source(str, del) as
        (select p_1, p_2 from sysibm.sysdummy1),
            target(str, del) as
            (select source.str, source.del from source
                where length(source.str) > 0
         union all
            select 
            (case when (instr(target.str, target.del) > 0) 
                                    then substr(target.str, 
                                                 instr(target.str, target.del)+1, 
                                                   length(target.str)-instr(target.str, target.del))                                  else null end),
                (case when (instr(target.str, target.del) > 0) 
                                              then target.del else null end)
                from target
                where length(target.str) > 0
                )
        select str from target
        where str is not null;
END
Noel
  • 10,152
  • 30
  • 45
  • 67
Jaruek R.
  • 11
  • 3
0

If your DB2's version can do it, you can use then LOCATE_IN_STRING function for to found position of your separator. The LOCATE_IN_STRING function returns the starting position of a string and enable you to choice the Nth instance. You can found documentation of this function here

For your example, you can use this code :

select 
substring(col, LOCATE_IN_STRING(col, '$', 1, 3), LOCATE_IN_STRING(col, '$', 1, 4) - LOCATE_IN_STRING(col, '$', 1, 3))                       
from MYTABLE
Esperento57
  • 16,521
  • 3
  • 39
  • 45
0
   substr(e.data,1,13) as NNSS,
   substring(e.data, LOCATE_IN_STRING(e.data, ';', 1, 1, CODEUNITS32)+1,  (LOCATE_IN_STRING(e.data, ';', 1, 2, CODEUNITS32) - LOCATE_IN_STRING(e.data, ';', 1, 1, CODEUNITS32)-1) ) as Name,  
   substring(e.data, LOCATE_IN_STRING(e.data, ';', 1, 2, CODEUNITS32)+1,  (LOCATE_IN_STRING(e.data, ';', 1, 3, CODEUNITS32) - LOCATE_IN_STRING(e.data, ';', 1, 2, CODEUNITS32)-1) ) as Vorname,
   substring(e.data, LOCATE_IN_STRING(e.data, ';', 1, 3, CODEUNITS32)+1,  (LOCATE_IN_STRING(e.data, ';', 1, 4, CODEUNITS32) - LOCATE_IN_STRING(e.data, ';', 1, 3, CODEUNITS32)-1) ) as Grund
Dolfin
  • 1