Geek Answers Handbook

Divide VARCHAR in DB2 to get the value inside

I have a VARCHAR column containing 5 data (2 CHAR(3) and 3 TIMESTAMP ) separated by a ' $ ' character.

 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 fourth field ...

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

... have something like

 SELECT SPLIT(COL, '$', 4) FROM MYTABLE 1 ----- 'null' 'null' '2009-04-10 10:50:00'

I am looking in this order:

  • DB2 String Function
  • An embedded statement such as SUBSTR(COL, POSSTR(COL)+1)...
  • A custom function that behaves like a SPLIT

Accuracy: Yes, I do know that it is not good to have such columns ...

+7
string split sql db2
source share
4 answers
 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!

Using:

 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;
+6
source share

I am sure there is a better way to write this, but here is 1 (SQL) solution for a simple case. It can be rewritten as a stored procedure to search for any arbitrary string. There may also be some third-party tools / extensions that will help you with the section 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
+4
source share

try it, 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
+1
source share

If your version of DB2 can do this, you can use the LOCATE_IN_STRING function to find the position of your delimiter. The LOCATE_IN_STRING function returns the starting position of the string and allows you to select the Nth instance. You can find documentation on this feature here.

In 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
0
source share

More articles:


All Articles