RegEx in SSMS: A gift for quick code

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!

7 thoughts on “RegEx in SSMS: A gift for quick code

  1. Greg Moore

    REGEX in SSMS is one of the most powerful tools out there! So glad I sat through Sean’s session on this several SQL Saturday’s ago.

    In fact I’m using this right now to “fix-up” a data dictionary I’ve got to turn it into a table
    I get

    INVITATION_ID NUMBER 22
    REQUEST_ID NUMBER 22
    CARRIER_ID NUMBER 22
    INVITATION_STATUS_ID NUMBER 22
    INVITATION_STATUS_DESC VARCHAR2 200
    RAP_RATING_METHOD_ID NUMBER 22
    RAP_RATING_METHOD_DESC VARCHAR2 200
    CREATED_BY_GUAR_MATCH_IND NUMBER 22
    QUESTIONNAIRE_IND NUMBER 22
    QUOTE_INTENT_ID NUMBER 22
    QUOTE_INTENT_DESC VARCHAR2 200
    QUOTE_INTENT_DATE DATE 21
    QUOTE_INTENT_TEXT VARCHAR2 4000
    QUOTING_USER_EMAIL_ADDR VARCHAR2 100
    INITIAL_INVITE_DATE DATE 21
    INSTRUCTIONS VARCHAR2 2000
    INITIAL_VIEW_DATE DATE 21
    INITIAL_DOWNLOAD_DATE DATE 21
    CREATE_DATE DATE 21
    LAST_MODIFIED_DATE DATE 21

    And then with a Find/Replace of:

    date:b:z -> date null,
    Number:b22 -> integer null,
    varchar2:b{:z} -> varchar(\1) null,
    Bam, becomes trivial.

    Another great example.

  2. Pingback: Regex In SSMS – Curated SQL

  3. Wayne

    Great example! I haven’t used tagged expressions before, but now that I’ve seen it, you can bet that I will be.
    Thanks, and keep the tips coming

  4. Pingback: Dew Drop - January 2, 2018 (#2634) - Morning Dew

  5. Ivan

    It is not working for me on SQL Server 2016 Management Studio

    Find all “^DELETE FROM {.+} WHERE {.+}$”, Regular expressions, Find Results 1, Current Document
    Matching lines: 0

    Is it just me?

    1. Patrick

      Not just you!

      The RegEx expression given will work for older versions of SSMS but not the latest versions V16 or Higher.

      Needs to change to
      ^DELETE FROM (.+) WHERE (.+)$

      and

      IF EXISTS (SELECT * FROM $1 WHERE $2) SELECT ‘$1 has undeleted rows!’ as TableName;

Comments are closed.