CODEWITHSUNDEEP
regexselectdb2
Manoj
Manoj

Reputation: 5612

RegEx in the select for DB2

I have a table with one column having a large json object in the format below. The column datatype is VARCHAR 

column1
--------
{"key":"value",....}

I'm interested in the first value of the column data

in regex I can do it by .*?:(.*),.* with group(1) giving me the value

How can i use it in the select query

Upvotes: 1

Views: 6569

Answers (4)

Aidanh
Aidanh

Reputation: 1

In DB2 for z/OS you will have to pass the variable to XMLQUERY with the PASSING option

db2 "with val as (
 select t.text
 from texts t
 where xmlcast(xmlquery('fn:matches($TEXT,''^[A-Za-z 0-9]*$'')'
               PASSING t.text as "TEXT") as integer) = 0
)
select * from val"

Upvotes: 0

AngocA
AngocA

Reputation: 7693

You can use regular expression with xQuery, you just need to call the function matches from a SQL query or a FLORW query.

This is an example of how to use regular expressions from SQL:

db2 "with val as (
 select t.text
 from texts t
 where xmlcast(xmlquery('fn:matches(\$TEXT,''^[A-Za-z 0-9]*$'')') as integer) = 0
)
select * from val"

For more information:

Upvotes: 1

user918176
user918176

Reputation: 1800

Don't do that, it's bad database design. Shred the keys and values to their own table as columns, or use the XML data type. XML would work fine because you can index the structure well, and you can use XPATH queries on the data. XPATH supports regexp natively.

Upvotes: 2

dan1111
dan1111

Reputation: 6566

DB2 doesn't have any built in regex functionality, unfortunately. I did find an article about how to add this with libraries:

http://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html

Without regexes, this operation would be a mess. You could make a function that goes through the string character by character to find the first value. Or, if you will need to do more than this one operation, you could make a procedure that parses the json and throws it into a table of keys/values. Neither one sounds fun, though.

Upvotes: 0

Related Questions