Thursday, June 6, 2013

Netezza Regex

I had to update a column on a netezza table to the first 5 digits that came after a string like so "TID=" But TID could be lowercase, uppercase etc.

So for eg, Tid=31564abc, and I wanted to extract the "31564" portion. Here's how I did it.

update MYTABLE
set MYCOLUMN=  regexp_extract_sp(ColA, '(tid|tID|ntID|ntid|NtID|ntid)=([0-9]{1,5})(&|\b)',1,3)
where adddate >= '2013-03-26 00:00:00'
and adddate < '2013-03-27 00:00:00'
and lower(colA) like '%tid%'

Note: this feature is only available if you installed SQL Extension Toolkit