Teradata Regular Expressions and Examples

  • Post author:
  • Post last modified:November 20, 2019
  • Post category:General
  • Reading time:3 mins read

The Teradata regular expressions 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.

Teradata Regular Expressions

In this article, we will check some of commonly used Teradata regular expressions.

Read:

Teradata Substring Regular Expression – REGEXP_SUBSTR

This function is used to extracts a substring from source_string that matches a regular expression specified by regexp_string.

SELECT REGEXP_SUBSTR('fruit chocolate chip', 'ch(i|o)p', 1, 1, 'c');

REGEXP_SUBSTR('fruit chocolate chip','ch(i|o)p',1,1,'c')
chip

Teradata Replace Regular Expression – REGEXP_REPLACE

This function replaces portions of source_string that match regexp_string with the replace_string.

SELECT REGEXP_REPLACE('Hello World World', '(world)

Teradata In string Regular Expression – REGEXP_INSTR

This function searches source_string for a match to regexp_string.

SELECT REGEXP_INSTR('This is the in string example','in string', 1, 1, 1, 'c');
REGEXP_INSTR('This is the in string example','in string',1,1,1,'c')
22

SELECT REGEXP_INSTR('Hello Santa says ho ho','Hello Santa says ho ho', 1, 1, 1, 'c');
REGEXP_INSTR('Hello Santa says ho ho','Hello Santa says ho ho',1,1,1,'c')
23

Teradata Similar Regular Expression – REGEXP_SIMILAR

This function compares source_string to regexp_string and returns integer value.

select REGEXP_SIMILAR(name, '(John B(i|y)rd)| (Tim B(i|y)rd)', 'c')
from (
select 'John Bird' as name
) a;
REGEXP_SIMILAR(name,'(John B(i|y)rd)| (Tim B(i|y)rd)','c')
1

Teradata Similar Regular Expression – REGEXP_SPLIT_TO_TABLE

This function splits source_string into a table of strings using regexp_string as the delimiter.

Read:

SELECT * from table(regexp_split_to_table
('Smart Phones','Motorola&Microsoft&Google&Apple','&','c')
returns (outkey varchar(30), token_ndx integer, token varchar(100))) as t1;

outkey               token_ndx      token
Smart Phones  1                    Motorola
Smart Phones  2                    Microsoft
Smart Phones  3                    Google
Smart Phones  4                    Apple
, 'My', 1, 1, 'i');















	Tags: