I want to use SQL on DB2 for i (formerly known as iSeries or AS/400) to extract a value from a string.
The string contains JSON-like list of delimited key:value pairs. Example:
("EventType":"XYZ","EffectiveDate":20131000,"ClientNo":2012020860902)
Given a key string such as "Event Type", I want to find the value after the colon (':'), which in this case would be "XYZ". But please keep in mind that my key string might not always be "Event Type".
SELECT SUBSTR( str,
INSTR(str, ':') + 1,
INSTR(str, ',') - INSTR(str, ':') - 1
) AS str_result
FROM
(
SELECT '("EventType":"XYZ","EffectiveDate":20131000,"ClientNo":2012020860902)' str
FROM sysibm.sysdummy1
) t
;
Since INSTR() isn't available, it can't be used with DB2 on i. However, POSSTR() can be used and it can locate the position of a search-string within a string. The LENGTH() function can give the length of the search string. Those two values together essentially tell where the colon will be.
If you know where the colon is, the position of the next comma can be determined; and that indicates the length of the substring to extract.
The position of a desired search-string paired value might use:
POSSTR(str, 'EffectiveDate') +
LENGTH('EffectiveDate') + 2
The "+ 2" gets us past the closing quote of the search-string and the colon. We're at the first character of the paired value. Let's call this expression 'exprA'. (Of course, the search-string itself would probably be a host variable instead of a literal constant.)
With the value of exprA, we can extract starting from that position and extending to the end of the full string. At this moment we don't need to know the length of the paired value. We'll reference this simply to give us good starting point for finding the next comma. That comma will tell us the length of the paired value.
SUBSTR( str, exprA )
Let's call this 'exprB'. It will give an intermediate value that has our desired paired value starting in the left-most position. Now we can think of something like this:
POSSTR( exprB, ',' ) - 1
That locates the first comma in our extracted substring. With "- 1", it steps back to the ending character of the paired value and effectively provides its length. We'll call this 'exprC'. And now we can think of some code looking like this:
SUBSTR( exprB ,
1 ,
exprC
)
We generate exprB to get our initial substring with our desired paired value in position 1, and we take a substring out of that by using exprC to provide the length. Now we need to expand our expressions and look at the whole mess:
SELECT SUBSTR(
SUBSTR( str,
POSSTR(str, 'EffectiveDate') +
LENGTH('EffectiveDate') + 2
) ,
1 ,
POSSTR( SUBSTR( str,
POSSTR(str, 'EffectiveDate') +
LENGTH('EffectiveDate') + 2
),
','
) - 1
) AS str_result
What's needed now is something that the SELECT can select FROM. We can use the phrase provided above:
FROM
(
SELECT '("EventType":"XYZ","EffectiveDate":20131000,"ClientNo":2012020860902)' str
FROM sysibm.sysdummy1
) t
;
However, there's still one minor problem remaining. There are three example value pairs. The first two will be handled okay, but the last pair in any series won't have a trailing comma for 'exprC' to locate; the POSSTR() function will return zero. It could be handled by adding an ugly CASE structure into the mess, but it's already seeming complicated enough. Easiest might be simplt to ensure that a trailing comma is present. And if the sample value is valid, easiest way to do that might be:
REPLACE( '("EventType":"XYZ","EffectiveDate":20131000,"ClientNo":2012020860902)' , ')', ',' )
Replace the right paren with a comma, and all should work.
However, it seems to me that processes such as this may be better done by some fairly straightforward program code. Create an external function and be done with it.
I second the motion for making a function to do the key / value parsing, so you don't end up with code all over the place doing this. I don't agree with the tendency of many people who feel that said function has to reside outside the database in a high level language. If you make an SQL function to perform the key / value parsing, preferably a table function because they are more flexible than scalar functions, any language or requestor application accessing the database has access to that function, and a reference to that table function can even be bundled in the same SQL statement that is going to consume the parsed value(s). Granted, a client side key / value HLL parser also should be present for those applications that need one and don't need to reference the database.
