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:
- Download and install Netezza SQL toolkit extension
- IBM Netezza Update Join Syntax and Examples
- Netezza Extract Functions and Examples
- Netezza Extract Numbers using Regular Expressions
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