RegEx in SSMS: A gift for quick code

Articles, Tech / By

I had yet another example of how beautiful Regular Expressions can be in SQL coding today, and so I thought I’d share.

What I HAD was a set of DELETE statements that might or might not have been executed recently:

DELETE FROM dbo.Table1 WHERE val = 1
DELETE FROM dbo.Table2 WHERE val = 1
DELETE FROM dbo.Table3 WHERE val = 1
DELETE FROM dbo.Table4 WHERE val = 1
DELETE FROM dbo.Table5 WHERE val = 1
DELETE FROM dbo.Table6 WHERE val = 1 and x = y;
DELETE FROM dbo.Table7 WHERE val = 1
DELETE FROM dbo.Table8 WHERE val = 1 and val2 in (select val from Table8)
DELETE FROM dbo.Table9 WHERE val = 1
DELETE FROM dbo.Table10 WHERE val = 1
DELETE FROM dbo.Table11 WHERE val = 1
DELETE FROM dbo.Table12 WHERE val = 1
DELETE FROM dbo.Table13 WHERE val = 1
DELETE FROM dbo.Table14 WHERE val = 1

What I needed was to be able to check each one of those tables, and see if any of the should-be-deleted rows still exist. Of course, I can certainly type this all out by hand…

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'Table2 has undeleted rows!' as TableName;

*Sigh*. I’m already  bored. And in reality, I had 45 of these statements, not 15. I’m not a big fan of repetitive, monkey-button work.

RegEx to the Rescue!

We can turn those statements into a formula, and do a replace-all! That’s what RegEx is great at. We can look at a string like that DELETE statement, and formulize it as pseudocode:

Start of line + 'DELETE FROM ' + table name + ' WHERE ' + where clause + end of line.

With just a little bit of know-how, turn this into a proper (SSMS flavor of) regular expression:

^DELETE FROM {.+} WHERE {.+}$

Here’s a quick key to these expressions:

  • ^ – start of line
  • {} – tagged expression
  • . – any character
  • * – one or more (in this case, one or more of “any character”)
  • $ – end of line

See? Direct translation! I’m strictly including start of line and end of line to be sure there’s no confusion whatever…I want this formula to apply to a single line of text.

Now, what I want to change each of these to is that IF EXISTS statement, which we can formulize like this:

'IF EXISTS (SELECT * FROM ' + table name + ' WHERE ' + where clause + ') SELECT ''' + table name + ' has undeleted rows!'' as TableName;'

Just a little more know-how, and we get this regular expression:

IF EXISTS (SELECT * FROM \1 WHERE \2) SELECT '\1 has undeleted rows!' as TableName;

Here’s a quick key to these expressions:

  • \1 – First tagged expression (in this case, we’d tagged the characters between FROM and WHERE, which means we tagged the table name)
  • \2 – Second tagged expression (the letters between WHERE and end of line, the where clause)

Note that you don’t have to put the beginning of line/end of line markers as part of the Replace With text.

Here’s what all this looks like in SSMS. Remember to select “Use Regular Expressions” in the find and replace dialogue!

SSMS Find and Replace window with regular expressions

And here are the results of the replace all, which require just a touch of manual editing (notice the semicolon in the middle of the Table6 statement). A touch of manual editing is WAY better than typing out 45 of these statements by hand!

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'dbo.Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'dbo.Table2 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table3 WHERE val = 1) SELECT 'dbo.Table3 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table4 WHERE val = 1) SELECT 'dbo.Table4 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table5 WHERE val = 1) SELECT 'dbo.Table5 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table6 WHERE val = 1 and x = y;) SELECT 'dbo.Table6 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table7 WHERE val = 1) SELECT 'dbo.Table7 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table8 WHERE val = 1 and val2 in (select val from Table8)) SELECT 'dbo.Table8 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table9 WHERE val = 1) SELECT 'dbo.Table9 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table10 WHERE val = 1) SELECT 'dbo.Table10 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table11 WHERE val = 1) SELECT 'dbo.Table11 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table12 WHERE val = 1) SELECT 'dbo.Table12 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table13 WHERE val = 1) SELECT 'dbo.Table13 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table14 WHERE val = 1) SELECT 'dbo.Table14 has undeleted rows!' as TableName;

See there? If we know just a few symbols, and take it step-by-step, we can save ourselves a ton of time! And this is but one example of an infinite number of uses for RegEx in SSMS.

Watch our RegEx videos on MidnightDBA.com, especially the session given at CACTUSS 2017!