Netezza Regular Expression Functions and Examples

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

The Netezza regular expression functions identify precise patterns of characters and are useful for extracting string from the data and validation of the existing data, for example, validate date, range checks, checks for characters, and extract specific characters from the data. All these Netezza regular expressions are added in the Netezza SQL extension toolkit.

Read:

In this article, we will check out some of the Netezza regular expression functions and some examples.

Netezza regexp_extract() function

The Netezza regexp_extract() function returns the matching text item in the string or data.

TRAINING.ADMIN(ADMIN)=> select system..regexp_extract('This is regular extract testing string', '..g',1,1); 
 REGEXP_EXTRACT 
---------------- 
 reg 
(1 row)

Netezza regexp_extract_all() function

The regexp_extract_all() function returns, in a varchar or nvarchar array, all matching texts items in the string or data. You have to use Netezza array_functions to display the data.

TRAINING.ADMIN(ADMIN)=> select system..array_combine(system..regexp_extract_all('the Hadoop and pydoop are related to python','(Ha|py)doop'),'|'); 
 ARRAY_COMBINE 
--------------- 
 Hadoop|pydoop 
(1 row)

Netezza regexp_extract_sp() function

The Netezza regexp_extract_sp() function is a regular expression against varchar or nvarchar input, returning the specified subpattern from the string or data.

For examples Read, Netezza extract numbers using Regular Expressions

Netezza regexp_instr() function

The Netezza regexp_instr() function returns the index of the matching text item from the string or data.

TRAINING.ADMIN(ADMIN)=> select system..regexp_instr('This is Netezza test!!', '.t',1,1); 
 REGEXP_INSTR 
-------------- 
 10 
(1 row)

Netezza regexp_like() function

The regexp_like() function returns t (true) if there is at least one matching occurrence in the input string or data.

TRAINING.ADMIN(ADMIN)=> select system..regexp_like('my user id is 091234 or 0239daz6','[0-9][^0-9]+[0-9]$'); 
 REGEXP_LIKE 
------------- 
 t 
(1 row)

Netezza regexp_replace() function

The regexp_replace() function replaces each instance of a pattern in the input with the value in the varchar or nvarchar replacement.

For examples Read, Netezza extract numbers using Regular Expressions

Netezza regexp_replace_sp() function

Netezza regexp_replace_sp() function is used to replace the string patters.

For examples Read, Netezza extract numbers using Regular Expressions