Recently, in one of our projects, we had to convert PL/SQL scripts to T-SQL for a reporting project. The PL/SQL scripts were very long, containing a lot of functions used repetitively with different parameters. To save some time and to make the task more interesting and fun, I used regular expressions to search and replace the functions wherever possible. In SQL Server Management Studio, Regular expressions can be used to search the code for a pattern and modify it by using "Find and Replace" window. To enable the use of Regular Expressions open "Find and Replace" window, select the Use option under Find Options and choose Regular expressions. This article is about a few instances where Regular Expressions can be applied.

1) PL/SQL Script 1:

Let us start with the simple ones. The code snippet below needs some formatting changes and modification to one function.

   UPDATE prod_group 
 SET prod_category = 'GROCERY'
 WHERE (upper(prod_name) LIKE '%FROZEN%'
 or upper(prod_name) like '%CANNED%')
 AND PRODUCT_SUB = 'SUBA'
;
 
   UPDATE prod_group 
 SET prod_category = 'CLOTHING'
 WHERE (upper(prod_name) LIKE '%STAR%'
 or upper(prod_name) like '%CARTERS%')
 AND PRODUCT_SUB = 'SUBB'
;

To get rid of empty lines between the statements, we can use the expressions below. You might have to repeat the same several times to get rid of all the empty lines.

Search Expression: \n@\n

Replace Expression: \n

To get rid of spaces at the start of each line, we can use the expressions below. You might have to repeat the same several times to get rid of all the empty spaces.

Search Expression: \n:b@:b{[A-Za-z]*}

Replace Expression: \n\1

The UPPER function was removed as SQL Server comparisons were case insensitive for our instance. We can use the expressions below to replace the upper function with empty string. I used regex for this to make sure I am replacing just the functions and not an upper string that appears anywhere in the code.

The < in the search function makes sure that upper is the starting of a word. And because '(' has its own meaning in regular expressions, I used '\' as an escape character.

Search Expression: <upper\(

Replace Expression: (

After applying the above regex search and replace expressions, the code is converted as shown below:

T-SQL Script:

UPDATE prod_group 
SET prod_category = 'GROCERY'
WHERE ((prod_name) LIKE '%FROZEN%'
or (prod_name) like '%CANNED%')
AND PRODUCT_SUB = 'SUBA'
;
UPDATE prod_group 
SET prod_category = 'CLOTHING'
WHERE ((prod_name) LIKE '%STAR%'
or (prod_name) like '%CARTERS%')
AND PRODUCT_SUB = 'SUBB'
;

2) PL/SQL Script 2:

Let us look at a little more complicated example now which uses the pl/sql decode function. Suppose, you have several PL/SQL decode statements in the script, and each decode statement has a different number of parameters.

decode(s1.attribute1,'A',s1.attribute1,'B',s1.attribute2,'C',s1.attribute3,'D',s1.attribute4,'E',s1.attribute5,s1.attribute10)

We can replace it with a T-SQL CASE statement by using two steps below:

Step 1: Use the expressions below only once.

Search Expression: decode\({.*},{[^\,]*}\)

Replace Expression: CASE \1 \nELSE \2 \nEND

Step 2: Use the expressions below repetitively for several times until no matches are found

Search Expression: {(CASE ).*},{.*},{[^\,]*}

Replace Expression: \1 \nWHEN \2 THEN \3

The modified code will look like this:

T-SQL Script:

CASE s1.attribute1 
WHEN 'A' THEN s1.attribute1 
WHEN 'B' THEN s1.attribute2 
WHEN 'C' THEN s1.attribute3 
WHEN 'D' THEN s1.attribute4 
WHEN 'E' THEN s1.attribute5 
ELSE s1.attribute10 
END 

3) PL/SQL Script 3:

The script below uses PL/SQL functions (INSTR and SUBSTR) which have to be replaced with CHARINDEX and SUBSTRING T-SQL functions.

UPDATE ITEMS
SET Attribute2 = SUBSTR(Attribute1,InStr(Attribute1,';')+1,100)
where (substr(Attribute1,1,4))= 'AAAA'
;

If there are nested functions, I suggest searching and replacing one occurrence after another rather than using Replace All. You can thus validate if your expression is doing what is expected. For the example above, to replace the InStr function with CharIndex I used the below expressions. It basically swaps the parameters.

Search expression: (INSTR)\({[^\(\)]*},{[^\(\)]*}\)

Replace Expression: CHARINDEX(\2,\1)

Use the below to replace SUBSTR with SUBSTRING

Search Expression :<SUBSTR\(

Replace Expression: SUBSTRING(

Before applying any of the regular expressions, I suggest doing a thorough testing of the functionality in a new window.

The modified code will look like this:

T-SQL Script:

UPDATE ITEMS
SET Attribute2 = SUBSTRING(Attribute1,CHARINDEX(';',Attribute1)+1,100)
where (SUBSTRING(Attribute1,1,4))= 'AAAA'
;

Summary

Regular Expressions can be used in SSMS for different purposes like formatting the code, modifying a result to a set of update statements or search for some pattern in the code. Searching and Replacing using regular expressions saves a lot of time especially for very lengthy scripts with some repetitive patterns of code which needs to be replaced. This article gives a few such basic examples. There are many more features available, and the syntax can be learned using the Reference List provided in SQL server Books Online.

References

• http://msdn.microsoft.com/en-us/library/ms174214.aspx
• http://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/