-6

Source string: " LTRIM(RTRIM(AB.ITEM_ID)) AS ITEM_NUMBER, "

convert LTRIM RTRIM to just TRIM and remove extra right parentheses. I need this to be converted to " TRIM(AB.ITEM_ID) AS ITEM_NUMBER, "

Have many sql files which are written long back and need to be corrected as part of platform migration. this source string could be tons of places in each sql. I tried doing line.replace('LTRIM(RTRIM', 'TRIM') works but leaves with the extra right bracket. I want the extra brackget also to be removed at oneshot sample inputs:

1.REPLACE(LTRIM(RTRIM(NVL(AB.ITEM_SHORT_DESC,AB.ITEM_DESC))),',','') AS SHORT_DESC

2.LTRIM(RTRIM(AB.ITEM_ID))** AS ITEM_NUMBER,**

please be advised the input is a full file as string and can have multiple occurrences and not just one line with this LTRIM(RTRIM . Sure have to use some regex etc.

I am using python 3.8.2

Rayudu Addagarla
  • 1
  • 3
  • Python does not have an AS opperator in this context? What is the AS trying to accomplish? – DownloadPizza Jul 24 '20 at 21:24
  • is this VBA code? or excel formulas? – Joran Beasley Jul 24 '20 at 21:27
  • The only problem is you don't know where the right parenthesis really is. –  Jul 24 '20 at 21:28
  • And you would have to use the new `import regex` module to use recursion to find and identify where the right parenthesis is. Even using recursion would only allow you to do the _outter_ LTRIM(RTRIM stuff If you wanted to do the inner stuff, you'd have to _re-run_ the regex on the string until there is no LTRIM(RTRIM's left I can give you the recursive regex if you wifll use it. –  Jul 24 '20 at 21:32

1 Answers1

0

You can use the Python replacement regex engine to do recursion and
the replacement you want.

Regex r"LTRIM\(RTRIM(\((?:(?>(?!LTRIM\(RTRIM\(|[()])[\S\s])+|\(|(?R))*\))\)"
Replace with r"TRIM\1"

Sample

import regex

src = '''
.. LTRIM(RTRIM(AB.ITEM_ID)) AS ITEM_NUMBER,
 .. REPLACE(LTRIM(RTRIM(NVL(AB.ITEM_SHORT_DESC,AB.ITEM_DESC))),',','') AS SHORT_DESC
.. LTRIM(RTRIM(AB.ITEM_ID))** AS ITEM_NUMBER,**
'''

srcnew = regex.sub(r"LTRIM\(RTRIM(\((?:(?>(?!LTRIM\(RTRIM\(|[()])[\S\s])+|\(|(?R))*\))\)", r"TRIM\1", src)

print( srcnew )

see https://repl.it/repls/DelightfulSatisfiedCore#main.py