Netezza Extract Numbers using Regular Expressions

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

IBM has introduced some of new functions in its latest version of Netezza. The new features include bunch of useful regular expression. All these new functions are available as a part of Netezza SQL toolkit extension. In this article, we will discuss about Netezza extract numbers using regular expressions.

Read:

Netezza Extract Numbers using Regular Expressions

Below are the some of the examples for Netezza extract number from string. There are lot of requirements that required extracting part of string from the data available in the table. Here are some of the common uses of Netezza regular expressions with an examples;

Netezza Extract 6 digit’s numbers from string examples

Here is the example to extract the 6digit’s number from string using Netezza regular expressions:

Query:

select string , SQL_TOOLKITDB..regexp_extract_sp(string,'\d{6}',1,1)
from (select 'My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a ;

Result:

=> \e
 STRING | REGEXP_EXTRACT_SP
---------------------------------------------------------------------+-------------------
 My zip is 12345 and id is 389362. Send details to house # 8/22 | 389362
(1 row)

Netezza Extract Numbers from the string examples

Here is the example to extract the number from the string using Netezza regular expressions:

Query:

select string ,
SQL_TOOLKITDB..regexp_replace(string, '[^[:digit:]]', ' ')
from (select ' My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a;

Result:

=> \e
 STRING | REGEXP_REPLACE
----------------------------------------------------------------------+----------------------------------------------------------------------
 My zip is 12345 and id is 389362. Send details to my house # 8/22 | 12345 389362 8 22
(1 row)

Another way to extract the numbers from the string using Netezza regular expressions

Query:

select string ,
SQL_TOOLKITDB..regexp_replace(string, '[^0-9]', ' ')
from (select ' My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a;

Result:

=> \e
 STRING | REGEXP_REPLACE
----------------------------------------------------------------------+----------------------------------------------------------------------
 My zip is 12345 and id is 389362. Send details to my house # 8/22 | 12345 389362 8 22
(1 row)

Netezza Extract Alphanumeric from the string examples

Query:

select system..regexp_extract('abc jjs Updates U 123 U389362','U[0-9][0-9][0-9][0-9][0-9][0-9]',1,1);

Result:

REGEXP_EXTRACT 
----------------
 U389362
(1 row)
Tags: