REGEXP_SIMILAR IN TERADATA

Share via:
REGEXP_SIMILAR IN TERADATA

Dear Readers,

In this article, we will learn about REGEXP_SIMILAR IN TERADATA

Purpose

Compares source_string to regexp_string and returns integer value.

REGEXP_SIMILAR supports 2 or 3 parameters.

Syntax

TD_SYSFNLIB.

Name of the database where the function is located.

source_string

A character argument.

If source_string is NULL, NULL is returned.

regexp_string

A character argument.

If regexp_string is NULL, NULL is returned.

match_arg

A character argument.

Valid values are:

  • ‘i’ = case-insensitive matching.
  • ‘c’ = case sensitive matching.
  • ‘n’ = the period character (match any character) can match the newline character.
  • ‘m’ = source_string is treated as multiple lines instead of as a single line. With this option, the ‘^’ and ‘$’ characters        apply to each line in source_string instead of the entire source_string.
  • ‘l’ = if source_string exceeds the current maximum allowed source_string size (currently 16 MB), a NULL is returned        instead of an error. This is useful for long-running queries where you do not want long strings causing an error              that  would make the query fail.
  • ‘x’ = ignore whitespace.

The argument can contain more than one character. If a character in the argument is not valid, then that character is ignored.

If match_arg is not specified, is NULL, or is empty:

The match is case-sensitive.

A period does not match the newline character.

source_string is treated as a single line.

Argument Types and Rules

Expressions passed to this function must have the following data types:

  • source_string = CHAR, VARCHAR, CLOB
  • regexp_string = CHAR, VARCHAR
  • match_arg = VARCHAR

The source_string maximum size is:

For parameters that are Latin CHAR or VARCHAR, the maximum source size is 32000 bytes.

For parameters that are Unicode CHAR or VARCHAR, the maximum source size is 64000 bytes.

For parameters that are Latin or Unicode CLOBs, the maximum source size is 16 MB.

The regexp_string maximum pattern string size is:

For parameters that are Latin CHAR or VARCHAR, the maximum regexp_string size is 32000 bytes.

For parameters that are Unicode CHAR or VARCHAR, the maximum regexp_string size is 32000 bytes.

For parameters that are Latin CLOBs, the maximum regexp_string size is 30000 bytes.

For parameters that are Unicode CLOBs, the maximum regexp_string size is 60000 bytes.

The function returns an error if this size is exceeded unless match_arg = ‘l’ is specified. If this is specified, a NULL is returned instead of an error.

The x match option ignores whitespace characters in the pattern/regexp_string. By default, whitespace characters match themselves.

Result Type

REGEXP_SIMILAR is a scalar function whose return value data type in an integer value:

  • 1 (true) if the entire string matches regexp_arg
  • 0 (false) if the entire string does not match regexp_arg

Example

The following query:

returns the names from the customers table that match:

 

The matching is case sensitive.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment