Regular expressions

The Find and Replace window supports regular expressions (shortened as regex or regexp). These are character patterns that help you efficiently find or replace specific types of text.

To use regex syntax elements in the Find and Replace window:

  1. Select Use and choose Regular Expressions from the drop-down list.
  2. Click the Expression Builder button arrow button next to Find what and select a regular expression from the list.

The selected regex syntax element is inserted into Find what .

Regex in the Find and Replace window

Supported regex syntax elements

The following table describes the regular expression syntax elements available in the drop-down list.

Syntax Expression Description
. Any single character Matches any single character except a line break.
* Zero or more Matches zero or more occurrences of the preceding expression, finding all possible matches.
+ One or more Matches at least one occurrence of the preceding expression.
^ Beginning of line Anchors the match string to the beginning of a line.
$ End of line Anchors the match string to the end of a line.
< Beginning of word Matches only when a word begins at this point in the text.
> End of word Matches only when a word ends at this point in the text.
[] Any character in the set Matches any character within the []. To specify a range, use a dash (for example, [a-z]).
[^] Any character not in the set Matches any character not included in the set of characters following the ^.
| Or Matches either the expression before or after the vertical bar (|). For example, (database|management)system matches database system or management system.
\ Escape Special Character Matches the character that follows the backslash (\) as a literal. This allows you to find the characters used in regular expression notation, such as { and ^. For example, \\^ searches for the ^ character.
{} Tagged Expression Matches text tagged with the enclosed expression.
:i SQL identifier Matches the expression ([a-zA-Z_$][a-zA-Z0-9_$]*).
:q Quoted string Matches the expression (("([^"]*)")|('([^']*)')).
:b Space or Tab Matches either space or tab characters.
:z Integer Matches the expression ([0-9]+).

Examples

The following examples demonstrate how to use regular expressions in the Find and Replace window.

Example 1: Find WHERE clauses with quoted string comparisons

Regular expression

WHERE.*=.*'[^']*'

Explanation

This pattern matches WHERE clauses that compare columns to single-quoted string values:

  • WHERE.*=.* – Matches the keyword WHERE, followed by any characters, an equal sign (=), and additional characters.
  • '[^']*' – Matches a complete single-quoted string. [^']* matches any characters except single quotes, ensuring it captures the content between the opening and closing quotes.

Regex in the Find and Replace window

In this example, the search pattern WHERE.*=.*'[^']*' finds WHERE clauses containing column-to-string comparisons, such as WHERE city = 'Portland'.

Example 2: Find column names with common suffixes

Regular expression

:i_(id|name|price)

Explanation

This pattern matches SQL identifiers ending with common column name suffixes:

  • :i – Matches SQL identifiers.
  • _ – Matches a literal underscore character.
  • (id|name|price) – Matches one of the suffixes id, name, or price by using the OR operator (|).

Regex in the Find and Replace window

In this example, the search pattern :i_(id|name|price) uses regular expressions to find any occurrence of an underscore followed by either id, name, or price. This pattern will match text like _id, _name, and _price throughout all open documents. The vertical bar | acts as an OR operator, allowing the search to find any of the three alternatives.