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

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi, Should we configure (enable) anything from installation point of view inorder to use regex in netezza 6.0?

    ReplyDelete
  3. NETEZZA Online Training
    http://www.21cssindia.com/courses/netezza-developer-online-training-21.html
    IBM Netezza Overview
    Netezza Architecture
    Connecting to Netezza
    Databases
    Tables
    Database Objects
    Data Distribution
    Loading and Unloading Tables
    Statistics
    Zone Maps
    Clustered Base Tables
    21st Century providing Online training and support on All Technologies. If you are seeking training and support you can reach me on 91-9000444287. Online training by real time Experts. Call us 001-309-200-3848 for online training

    ReplyDelete