【Question】

I have a number of strings like this:

aaa/bbb/ccc/ddd/eee 

aaa/bbb/ccc 

aaa/bbb/ccc/xxx/123

I am using DB2 where I need to find the position of the 1^st slash and the 2^nd slash and then get data between them, and then find the positoon of the 3^rd slash and get data between it and the 2^nd slash, etc.

【Answer】

Find positions of the slashes using DB2’s locate function and then get the substring with substr function. To get the substring between the 1^st slash and the 2^nd slash:

select substr(str,locate(‘/’,str)+1,locate(‘/’,str,locate(‘/’,str)+1-locate(‘/’,str))-1) from stb

In the above locate function the 3^rd parameter gets the start position and the end position of a substring. A nested query is needed to find the position of the 2^nd or n^th slash. It’s hard to code all these locate query in one statement dynamically. It’s even harder to do it in the stored procedure.

SPL (Structured Process Language) offers related functions to handle it effortlessly. Here’s the SPL script:

:begin is the specified start position. For example, get data between the 2^nd slash and the 3^rd slash when begin’s value is 2 and you will have the following result:

esProc provides JDBC interface to be easily integrated into another application. Refer to How to Call an SPL Script in Java to learn more.