REGEXP_SIMILAR - Teradata Database

REGEXP_SIMILAR

Purpose  

Compares source_string to regexp_string and returns integer value:

REGEXP_SIMILAR provides semantics comparable to the SIMILAR predicate in ANSI-SQL-99.

REGEXP_SIMILAR supports 2 or 3 parameters.

Syntax  

where:

Syntax element …	Specifies …
TD_SYSFNLIB	the 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. If match_arg is not specified, the default is as follows: The match is case sensitive. A period does not match the newline character. source_string is treated as a single line. If match_arg is not present, NULL, or empty, the default ('c') is used. The default is also used if match_arg contains an invalid character.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Invocation

REGEXP_SIMILAR is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

Argument Types and Rules

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

source_string= CHAR, VARCHAR, CLOB

regexp_string = CHAR, VARCHAR (maximum size of 512 bytes)

match_arg = VARCHAR

source_string parameters that are CLOBs can be a maximum of 16 MB. 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.

You can also pass arguments with data types that can be converted to the above types using the implicit data type conversion rules that apply to UDFs.

Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.

For details, see “Compatible Types” in SQL External Routine Programming.

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:

SELECT name FROM customers WHERE REGEXP_SIMILAR(name, '(Mike B(i|y)rd)| (Michael B(i|y)rd)', 'c') = 1; 

returns the names from the customers table that match:

'Mike Bird'

'Mike Byrd'

'Michael Bird'

'Michael Byrd'

The matching is case sensitive.