Export (0) Print
Expand All

Search Text with Regular Expressions


Applies To: SQL Server 2016

Regular expressions are a concise and flexible notation for finding and replacing patterns of text. A specific set of regular expressions can be used in the Find what field of the SQL Server Management Studio Find and Replace dialog box.

To find using regular expressions

  1. To enable the use of regular expressions in the Find what field during QuickFind, FindinFiles, Quick Replace, or Replace in Files operations, select the Use option under Find Optionsand choose Regular expressions.

  2. The triangular Reference List button next to the Find what field then becomes available. Click this button to display a list of the most commonly used regular expressions. When you choose any item from the Expression Builder, it is inserted into the Find what string.

System_CAPS_ICON_note.jpg Note

There are syntax differences between the regular expressions that can be used in Find what strings and those that are valid in Microsoft .NET Framework programming. For example, in Find and Replace, the braces notation {} is used for tagged expressions. So the expression "zo{1}" matches all occurrences of "zo" followed by the tag 1, as in "Alonzo1" and "Gonzo1". But within the .NET Framework, the notation {} is used for quantifiers. So the expression "zo{1}" matches all occurrences of "z" followed by exactly one "o", as in "zone" but not "zoo".

The following table describes the regular expressions available in the Reference List.

Any character.Matches any single character except a line break.
Zero or more*Matches zero or more occurrences of the preceding expression, making 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.
Line break\nMatches a platform-independent line break. In a Replace expression, inserts a line break.
Any one character in the set[]Matches any one of the characters within the []. To specify a range of characters, list the starting and ending character separated by a dash (-), as in [a-z].
Any one character not in the set[^...]Matches any character not in the set of characters following the ^.
Or|Matches either the expression before or the one after the OR symbol (|). Mostly used within a group. For example, (sponge|mud) bath matches "sponge bath" and "mud bath."
Escape\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.
C/C++ Identifier:iMatches the expression ([a-zA-Z_$][a-zA-Z0-9_$]*).
Quoted string:qMatches the expression (("[^"]*")|('[^']*')).
Space or Tab:bMatches either space or tab characters.
Integer:zMatches the expression ([0-9]+).

The list of all regular expressions that are valid in Find and Replace operations is longer than can be displayed in the Reference List. You can also insert any of the following regular expressions into a Find what string:

Minimal — zero or more@Matches zero or more occurrences of the preceding expression, matching as few characters as possible.
Minimal — one or more#Matches one or more occurrences of the preceding expression, matching as few characters as possible.
Repeat n times^nMatches n occurrences of the preceding expression. For example, [0-9]^4 matches any 4-digit sequence.
Grouping()Groups a subexpression.
nth tagged text\nIn a Find or Replace expression, indicates the text matched by the nth tagged expression, where n is a number from 1 to 9.

In a Replace expression, \0 inserts the entire matched text.
Right-justified field\(w,n)In a Replace expression, right-justifies the nth tagged expression in a field at least w characters wide.
Left-justified field\(-w,n)In a Replace expression, left-justifies the nth tagged expression in a field at least w characters wide.
Prevent match~(X)Prevents a match when X appears at this point in the expression. For example, real~(ity) matches the "real" in "realty" and "really," but not the "real" in "reality."
Alphanumeric character:aMatches the expression ([a-zA-Z0-9]).
Alphabetic character:cMatches the expression ([a-zA-Z]).
Decimal digit:dMatches the expression ([0-9]).
Hexadecimal digit:hMatches the expression ([0-9a-fA-F]+).
Rational number:nMatches the expression (([0-9]+.[0-9]*)|([0-9]*.[0-9]+)|([0-9]+)).
Alphabetic string:wMatches the expression ([a-zA-Z]+).
Escape\eUnicode U+001B.
Bell\gUnicode U+0007.
Backspace\hUnicode U+0008.
Tab\tMatches a tab character, Unicode U+0009.
Unicode character\x#### or \u####Matches a character given by Unicode value where #### is hexadecimal digits. You can specify a character outside the Basic Multilingual Plane (that is, a surrogate) with the ISO 10646 code point or with two Unicode code points giving the values of the surrogate pair.

The following table lists the syntax for matching by standard Unicode character properties. The two-letter abbreviation is the same as listed in the Unicode character properties database. These may be specified as part of a character set. For example, the expression [:Nd:Nl:No] matches any kind of digit.

Uppercase letter:LuMatches any one upper case letter. For example, :Luhe matches "The" but not "the".
Lowercase letter:LlMatches any one lower case letter. For example, :Llhe matches "the" but not "The".
Title case letter:LtMatches characters that combine an uppercase letter with a lowercase letter, such as Nj and Dz.
Modifier letter:LmMatches letters or punctuation, such as commas, cross accents, and double prime, used to indicate modifications to the preceding letter.
Other letter:LoMatches other letters, such as gothic letter ahsa.
Decimal digit:NdMatches decimal digits such as 0-9 and their full-width equivalents.
Letter digit:NlMatches letter digits such as roman numerals and ideographic number zero.
Other digit:NoMatches other digits such as old italic number one.
Open punctuation:PsMatches opening punctuation such as open brackets and braces.
Close punctuation:PeMatches closing punctuation such as closing brackets and braces.
Initial quote punctuation:PiMatches initial double quotation marks.
Final quote punctuation:PfMatches single quotation marks and ending double quotation marks.
Dash punctuation:PdMatches the dash mark.
Connector punctuation:PcMatches the underscore or underline mark.
Other punctuation:PoMatches (,), ?, ", !, @, #, %, &, *, \, (:), (;), ', and /.
Space separator:ZsMatches blanks.
Line separator:ZlMatches the Unicode character U+2028.
Paragraph separator:ZpMatches the Unicode character U+2029.
Non-spacing mark:MnMatches non-spacing marks.
Combining mark:McMatches combining marks.
Enclosing mark:MeMatches enclosing marks.
Math symbol:SmMatches +, =, ~, |, <, and >.
Currency symbol:ScMatches $ and other currency symbols.
Modifier symbol:SkMatches modifier symbols such as circumflex accent, grave accent, and macron.
Other symbol:SoMatches other symbols, such as the copyright sign, the pilcrow sign, and the degree sign.
Other control:CcMatches end of line.
Other format:CfFormatting control character such as the bi-directional control characters.
Surrogate:CsMatches one half of a surrogate pair.
Other private-use:CoMatches any character from the private-use area.
Other not assigned:CnMatches characters that do not map to a Unicode character.

In addition to the standard Unicode character properties, the following additional properties may be specified as part of a character set.

Alpha:AlMatches any one character. For example, :Alhe matches words such as "The", "then", and "reached".
Numeric:NuMatches any one number or digit.
Punctuation:PuMatches any one punctuation mark, such as ?, @, ', and so on.
White space:WhMatches all types of white space, including publishing and ideographic spaces.
Bidi:BiMatches characters from right-to-left scripts such as Arabic and Hebrew.
Hangul:HaMatches Korean Hangul and combining Jamos.
Hiragana:HiMatches hiragana characters.
Katakana:KaMatches katakana characters.
Ideographic/Han/Kanji:IdMatches ideographic characters, such as Han and Kanji.

Search and Replace
Search Text with Wildcards

Community Additions

© 2016 Microsoft