Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Regular Expressions to Convert PL/SQL code to T-SQL

By P. Praveena, (first published: 2011/10/11)

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

Total article views: 9497 | Views in the last 30 days: 9
 
Related Articles
FORUM

derived column --replace " with space expression error

derived column --replace " with space expression error

FORUM

Expression

Expression

FORUM

WILD REPLACE ?

function that will replace text supporting wildcards

BLOG

Stuff VS Replace function in SQL Server

Stuff function: - This function is used to replace string from the given start position, passed ...

FORUM

Replace function accross entire table

Is it possible to apply the replace function accroos an entire table of data???

Tags
oracle    
pl/sql    
regular expressions    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones