Leverage Standard RegEx in SSMS for Powerful Searching

SQL Server Management Studio (SSMS) allows you to perform advanced search and replace operations using Regular Expressions (RegEx). These can be particularly helpful for cleaning up code, refactoring queries, or removing unnecessary characters. Below are some useful RegEx examples that you can apply to your SSMS queries.

---

1. Removing Blank Lines

If you’ve imported code from a source like Firefox, you might encounter unwanted blank lines. To remove these, you can search for:

\n @\n

And replace it with:

\n

---

2. Finding Un-commented 'INT' Instances

To find all instances of INT that aren't commented out, use:

^~(:b<--).:bINT>

This RegEx checks for lines that aren't preceded by a comment (--), making it easier to find INT instances in active code.

---

3. Selecting Up to 100 Characters in a Line

To select the first 100 characters of a line (and all characters if fewer than 100), use:

^(.^100)|(.*)

This will capture up to 100 characters at the start of the line and will fall back to matching the entire line if the line is shorter.

---

4. Inserting a String at a Specific Position

To insert a string at a specific column position (e.g., after the first 100 characters), use the following:

Find:

^{.^100}

Then, replace it with:

\1'I've inserted this'

This will insert the specified text after the first 100 characters.

---

5. Deleting Columns After a Specific Position

To delete 10 characters after column 100, use:

^{(.^100)}{.^10}

Replace with:

\1

This RegEx will preserve the first 100 characters while removing the next 10.

---

6. Finding Quoted or Delimited Strings

To find strings enclosed in either quotes or delimiters, use:

("[^"]*")|([[^[]])

This captures quoted strings as well as strings enclosed by square brackets.

---

7. Replacing Quoted Strings with Delimiters

To replace all quoted strings with delimited strings, use the following find and replace pattern:

Find:

("[^"]*")

Replace with:

[\1]

---

8. Removing Inline Comments

If you want to remove inline comments that take up the entire line, use:

^:b<--.>$

Replace with nothing. This RegEx will remove lines that only contain comments.

---

9. Finding Valid Object Names with Delimiters

To find valid object names within delimiters, use:

[{[A-Za-z_#@][A-Za-z0-9#$@_]*}]

And replace with:

\1

This will remove unnecessary delimiters while ensuring the object name is valid.

---

10. Finding 'Tibbling' Prefixes (tbl, vw, fn, usp)

To find object names that begin with prefixes like tbl, vw, fn, or usp, use:

<(tbl|vw|fn|usp)[A-Za-z0-9#$@_]*>

This RegEx helps identify objects using certain naming conventions.

---

11. Removing Tibbling Prefixes

To remove these prefixes (e.g., tbl or usp) from the object names, use:

Find:

<(tbl|vw|fn|usp){[A-Za-z0-9#$@_]*}>

And replace with:

\1

This will keep the object name while removing the prefix.

---

12. Matching Words with 3 or More Characters

If you need to match words that are at least 3 characters long, use:

<:c^3:c*>

This is a workaround for the lack of proper range quantifiers in SSMS.

---

13. Finding Multi-line Comments

To find multi-line comments that start with /* and end with */, use:

/*(:Wh|[^:Wh])*/

This RegEx works around the limitation of stopping at the end of a line.

---

14. Finding Title-Cased Words

To find words that start with a capital letter followed by lowercase letters, use:

<:Lu:Ll[a-zA-Z0-9_$]*>

This helps find properly capitalized words in your code.

---

15. Removing Headers in Code (e.g., SMO Comments)

To remove headers added by SMO (e.g., comments about object names and script dates), use:

Find:

/[*]^6.<[*]^*6/

Replace with nothing.

---

16. Commenting and Uncommenting Lines

• To comment out lines, select the lines and use:

  Find:

  ^

  Replace with:

  -- (or your comment character)

• To uncomment lines, use:

  Find:

  ^:b<--{.}*$

  Replace with:

  \1

---

17. Finding Two Words Separated by Up to Three Words

To find two words (e.g., FROM and AS) separated by up to three words, use:

\bFROM(?:\W+\w+){1,3}?\\W+AS\b

This RegEx allows for more flexibility in matching phrases between two keywords.

---

18. Finding the First Object Referenced by a FROM Clause

To find the first object referenced by a FROM clause, use:

^~(:b<--.){(:Wh|[^:Wh])#}&< (ON|CROSS|RIGHT|INNER|OUTER|LEFT|JOIN|WHERE|ORDER|GROUP|AS)

This matches keywords after FROM, such as INNER JOIN or WHERE.

---

19. Finding Integers or Floating-point Numbers

To find either integers or floating-point numbers, use:

~(. )<([+-]@[0-9]@.[0-9]E[+-][0-9])|([+-]@[0-9].[0-9]*)|([+-]@[0-9]+)

This captures both types of numeric values in your code.

---

Metacharacters Outside Square Brackets

Character	SSMS Equivalent	Meaning
\	\\	General escape character
^	^	Assert start of string or line
$	$	Assert end of string or line
.	.	Match any character except newline
[	[	Start character class
]	]	End character class
`	`	`
(	(	Start sub-pattern
)	)	End sub-pattern