CODEWITHSUNDEEP
regex
whytheq
whytheq

Reputation: 35557

Complex regex to transform from legacy sql code

Is it possible to transform sentences of the following form

IF OBJECT_ID('TEMPDB..#tmpX') IS NOT NULL DROP TABLE #tmpX;      (1)

To the following

DROP TABLE IF EXISTS #tmpX;                                      (2)

What I need to effectively is the following pseudo-code:

  1. Identify rows of code that are of the form (1)
  2. Extract the temporary table name - in this case #tmpX: The temp table name always begins # but can be any length (without spaces)
  3. Add the name found in step 2 to the string DROP TABLE IF EXISTS
  4. Append a semi-colon onto the result of 3.

I use sql-server but not interested in it's leftfield regex functionality - so will open the sql file in either notepad++ or komodo edit - so standard regex is what I'll use.

(apologies that I have no definite attempt included but my regex is very limited)

Upvotes: 0

Views: 71

Answers (1)

revo
revo

Reputation: 48711

Find:

IF \w+\(['"][^#]*(#\w+)['"]\) IS NOT NULL DROP TABLE \1;
  • [^#]*(#\w+) Match anything except # more or zero times then the table name.

Replace with:

DROP TABLE IF EXISTS $1;

Upvotes: 1

Related Questions