Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Operators and functions for matching strings against patterns. Use pattern matching to:
LIKE for simple pattern matching with % and _.LIKE for simple wildcard matching. Use regular expressions for complex patterns like repetition, alternation, and character classes.LIKE and regex operators have case-insensitive variants (ILIKE, ~*).LIKE matches the entire string by default. Regular expressions match anywhere within the string unless anchored with ^ and $.Simple pattern matching with wildcards:
% (any sequence) and _ (single character) wildcards.Match strings against regular expression patterns:
~, ~*, !~, and !~* operators.Functions for searching, extracting, and manipulating text with regular expressions. These use RE2/POSIX syntax.
Most characters match themselves. The following metacharacters have special meaning:
| Metacharacter | Description |
|---|---|
. | Matches any single character (except newline). |
| | Matches expressions to either side of it. |
\ | Match a metacharacter with this escape character |
Quantifiers specify how many times the preceding pattern must occur to match. By default, quantifiers are greedy, meaning they try to match as much text as possible.
| Quantifier | Description |
|---|---|
* | Zero or more |
+ | One or more |
? | Zero or one |
{n} | Exactly n times |
{n,} | At least n times |
{n,m} | Between n and m times |
Add ? after a quantifier for lazy matching: *?, +?, ??, {n,}?, {n,m}?. Lazy matching matches the least number of characters possible. For example, in the string <b>bold</b>, the greedy pattern <.*> matches the entire string. Whereas, the lazy pattern <.*?> only matches <b>.
The maximum number of repetitions for {n}, {n,}, {n,m} is 1000.
Note
Groups ( (...) ) combine characters and metacharacters into a single unit. You can use them to apply quantifiers and flags to sub-patterns. Use (?flags:pattern) syntax to apply flags to a group.
| Flag | Description |
|---|---|
i | Case-insensitive matching. |
c | Case-sensitive matching. |
q | Treats the pattern as a literal string. |
g | Tests against all characters in the string, not just the first match. |
For example, (?i:abc) sets the case-insensitive flag, so it matches abc, ABC, aBc, and more.
Character classes are a shorthand for common group patterns.
The most general form of a character class is [ ], matching any character contained in the square brackets. For example, you can abbreviate the pattern (a|e|i|o|u) to [aeiou]. Contiguous ranges of characters can be shortened by using a hyphen. For example, [a-e] is equivalent to [abcde]. The range notation disregards locale-specific collation.
You can negate character classes by placing a caret (^) after the opening bracket. For example, [^aeiou] matches any character that is not a vowel.
Most metacharacters have no special meaning inside a character class (For example, [.] matches a literal dot). The exceptions are \\, ^ (when at the start for negation), - (for ranges), and ]. To include a literal ] in a character class, put it first (for example, []abc]) or escape it [ab\\]c]. To include a literal -, put it first or last, or escape it.
Note
| Pattern | Description |
|---|---|
[[:alpha:]] | alphabetic characters ([A-Za-z]) |
[[:upper:]] | upper case letters ([A-Z]) |
[[:lower:]] | lower case letters ([a-z]) |
[[:digit:]], \d | digits ([0-9]) |
[[:xdigit:]] | hexadecimal digits ([0-9A-Fa-f]) |
\D | non-digits ([^0-9]) |
[[:alnum:]] | alphanumeric characters ([0-9A-Za-z]) |
[[:word:]], \w | word characters ([0-9A-Za-z_]) |
\W | non-word characters ([^0-9a-za-z_]) |
[[:space:]], \s | whitespace ([\t\n\f\r ]) |
\S | non-whitespace ([^ \t\n\f\r]) |
[[:blank:]] | blankspace ([\t ]) |
[[:punct:]] | punctuation ([!-/:-@[\-\`{-~;]) |
Anchors match a position rather than a character. These patterns can be used to anchor a match to a specific position in a string. For example, you can ensure that a given string fully matches a pattern by surrounding it with ^ and $ anchors.
| Anchor | Description |
|---|---|
^ | Start of string (or line, if m flag is set) |
\A | Start of string |
$ | End of string (or line, if m flag is set) |
\z | End of string |
\b | ASCII word boundary (\w on one side; \W, \A, or \z on the other) |
\B | Not an ASCII word boundary |
Find products starting with a specific prefix.
1SELECT name FROM products
2WHERE name LIKE 'Widget%';Search regardless of case.
1SELECT name FROM products
2WHERE name ILIKE '%cloud%';Extract phone numbers from text.
1SELECT regexp_substr(contact_info, '\d{3}-\d{3}-\d{4}') AS phone
2FROM customers;Check whether values match an expected format.
1SELECT email FROM users
2WHERE regexp_like(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');Remove unwanted characters.
1SELECT regexp_replace(phone, '[^0-9]', '') AS digits_only
2FROM contacts;
j