This example creates a table containing several strings to demonstrate regular expressions. If you are porting a regular expression query from an Oracle database, remember that Oracle considers a zero-length string to be equivalent to NULL, while Vertica does not. This function operates on UTF-8 strings using the default locale, even if the locale has been set to something else. All spaces in the regular expression that you want to be matched in strings must be escaped with a backslash ( \) character. Comments start with a hash ( #) character and end with a newline ( \n). Using the x modifier causes the function to ignore all unescaped space characters and comments in the regular expression. operator matches any character except a newline.Īdd comments to your regular expressions. Without the m modifier, the start and end of line operators match only the start and end of the string.Īllow the single character regular expression operator (. Using this modifier, the start of line ( ^) and end of line ( $) regular expression operators match line breaks ( \n) within the string. Treat the string to match as multiple lines. Treat strings as binary octets, rather than UTF-8 characters.įorce the match to be case sensitive (the default). One or more single-character flags that modify how the regular expression finds matches in string: b See the Perl Regular Expressions Documentation for details. The syntax of the regular expression is compatible with the Perl 5 regular expression syntax. If string exists in a _raw_ column of a flex or columnar table, cast string to a LONG VARCHAR before searching for pattern.Ī string containing the regular expression to match against the string.
The VARCHAR or LONG VARCHAR string to search for a regular expression pattern match. Syntax REGEXP_LIKE( string, pattern ) Parameters string This function is similar to the LIKE-predicate, except that it uses regular expressions rather than simple wildcard character matching. You can use the UPPER () or LOWER () function to make LIKE or NOT LIKE not case sensitive.Returns true if the string matches the regular expression. Netezza does not support ILIKE which is not case sensitive search. Netezza LIKE statement is case sensitive. In this case, there is no way to turn off the special meaning of underscore and percent signs in the pattern. It is also possible to select no escape character by entering ESCAPE ” (empty single quotation marks). SELECT * FROM Your_table WHERE col1 LIKE '%90#%%' escape '#' You can combine Netezza LIKE operator in multiple conditions: SELECT * FROM your_table WHERE LOWER(col1) LIKE 'ab% or LOWER(col1) NOT LIKE 'cd% Escape Character Examples SELECT * FROM Your_table WHERE col1 LIKE '%90\%%' SELECT * FROM your_table WHERE col1 NOT LIKE 'ab% Netezza LIKE statement Examples SELECT * FROM your_table WHERE col1 LIKE 'ab% In case if you want to match the escape character itself, enter two escape characters. You can also choose a different character by using the ESCAPE clause in the LIKE statement. The default escape character in the Netezza LIKE statement is the backslash character. To match a literal underscore or percent sign without matching other remaining characters in the string, you must precede the respective character in pattern with the escape character. To match a pattern anywhere within a string, the pattern must start and end with a percent sign. LIKE pattern matches always include the entire string.
Return everything that starts with ‘a’’abc.
‘abc’ LIKE ‘abc’ True : Equality operation‘abc’ LIKE ‘a’ True : Pattern matching. If the string does not contain any percentage sign or underscore, then pattern is the string itself, in this case LIKE acts as an equality operator. Return everything that starts with ‘a’’abc’ LIKE ‘_b_’ – True : Any single character matching ‘abc’ LIKE ‘c’ – False: does not match any condition Netezza LIKE Statement Patterns Matching. ‘abc’ LIKE ‘abc’ – True : Equality operation‘abc’ LIKE ‘a%’ – True : Pattern matching.