Find and replace text in all stored procedures (SQL Server)

Question

I have been asked to make changes in all the stored procedure in a SQL Server database to replace CONVERT(varchar( with RTRIM(CONVERT(varchar( and close with ).

For example - find this code:

CONVERT(varchar(5), col$1) + '~' + CONVERT(varchar(8), col$2) + '~AD~'

and replace it with:

RTRIM(CONVERT(varchar(5), col$1)) + '~' + RTRIM(CONVERT(varchar(8), col$2)) + '~AD~'

The find code is different in other stored procedure e.g.

convert(varchar(10), @date_ALTERd, 103)

but CONVERT(varchar( will be same in each find code.

There are 147 stored procedures and it is quite difficult to do one by one...

Can someone help ?

Thanks in advance for your help.

question from:https://stackoverflow.com/questions/66056322/find-and-replace-text-in-all-stored-procedures-sql-server

Answer 1

notepad++ and regex for the rescue : extract your procedure into plain text and put it in notepad++ then replace with some regex.

find:

(?i)CONVERT(.*?).*?)(?-i)

replace:

RTRIM($0)

and here is the example result:

convert(varchar(5), col$1) + '~' + CONVERT(varchar(8), col$2) + '~AD~'

after replace:

RTRIM(convert(varchar(5), col$1)) + '~' + RTRIM(CONVERT(varchar(8), col$2)) + '~AD~' 

if every string start with CONVERT( and end with ) this regex expression should work but be careful and check each one IMO. those should also take care of case sensitive and multiple match in one line.
if the string have more then two pairs of parentheses with in convert(...) then this will still fail, in that case it will be better just manual adjust. here is regex-101.