---
schema_version: "1"
id: f3486da55d287671a7b432759faf90ad90bee8cd170ef9ffbbaecd6fc7f121dd
title: String Pattern Matching
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/string-matching/string-matching.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/string-matching.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 888eaa85edfc2b6907e99e94e131ac0718cf18e9c97ba0192199415120fc2a2d
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# String Pattern Matching

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

Operators and functions for matching strings against patterns. Use pattern matching to:

- **Search with wildcards** using `LIKE` for simple pattern matching with `%` and `_`.
- **Match complex patterns** using regular expressions for advanced text processing.
- **Extract and replace** substrings based on regex patterns.
- **Count occurrences** of patterns within strings.

## Considerations

- **LIKE vs regex**: Use `LIKE` for simple wildcard matching. Use regular expressions for complex patterns like repetition, alternation, and character classes.
- **Case sensitivity**: Both `LIKE` and regex operators have case-insensitive variants (`ILIKE`, `~*`).
- **Full string matching**: `LIKE` matches the entire string by default. Regular expressions match anywhere within the string unless anchored with `^` and `$`.

## LIKE Pattern Matching

Simple pattern matching with wildcards:

- [LIKE / ILIKE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/like.md) - Match patterns using `%` (any sequence) and `_` (single character) wildcards.

## Regular Expression Operators

Match strings against regular expression patterns:

- [Regular Expression Match Operators](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regex-match.md) - The `~`, `~*`, `!~`, and `!~*` operators.

## Regular Expression Functions

Functions for searching, extracting, and manipulating text with regular expressions. These use RE2/POSIX syntax.

- [REGEXP_LIKE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-like.md) - Check whether a string matches a pattern.
- [REGEXP_REPLACE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-replace.md) - Replace substrings matching a pattern.
- [REGEXP_INSTR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-instr.md) - Find the position of a pattern.
- [REGEXP_SUBSTR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-substr.md) - Extract the substring matching a pattern.
- [REGEXP_COUNT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-count.md) - Count occurrences of a pattern.
- [REGEXP_SPLIT_TO_ARRAY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-split-to-array.md) - Split a string into an array using a regex delimiter.
- [REGEXP_MATCHES](https://developer.salesforce.com/docs/data/data-cloud-query-guide/set-returning-func/regexp-matches.md) - Return the captured groups of each match as a set of rows.

## Regular Expression 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

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>`.

**note:** The maximum number of repetitions for `{n}`, `{n,}`, `{n,m}` is 1000.

### Groups and Flags

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

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.

**note:** 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.

| 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

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                                               |

## Examples

### Simple Pattern Matching

Find products starting with a specific prefix.

```sql
SELECT name FROM products
WHERE name LIKE 'Widget%';
```

### Case-Insensitive Search

Search regardless of case.

```sql
SELECT name FROM products
WHERE name ILIKE '%cloud%';
```

### Extract Data with Regex

Extract phone numbers from text.

```sql
SELECT regexp_substr(contact_info, '\d{3}-\d{3}-\d{4}') AS phone
FROM customers;
```

### Validate Format

Check whether values match an expected format.

```sql
SELECT email FROM users
WHERE regexp_like(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
```

### Clean Data

Remove unwanted characters.

```sql
SELECT regexp_replace(phone, '[^0-9]', '') AS digits_only
FROM contacts;
```

## Related Documentation

- [String Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/string-func/string-func.md)