Working on a Python code, I had a requirement for removing the single/double quotes and open/close brackets from the string of below format —
>>> text = """with summary as (select ' ... 'p.col1,p.col2,p.col3, ROW_NUMBER() ' ... 'OVER(PARTITION BY p.col1,p.col3 ORDER BY ' ... 'p.col2) AS rk from (select * from (select ' ... 'col2, col1, col3, ' ... 'sum(col4) as col6 from ' ... '"demo"."tab1" a join ' ... "(select lpad(col5, 12, '0') as col5 from demo1.tab11) ta on " ... '(a.col1 = ta.col5) where col4 >= 0 and ' ... "(col3 like 'A%' or col3 in ('complete', 'random', " ... "'mobile')) group by col2, " ... 'col1, col3) where col6>=1 order by ' ... 'col2) p)\n' ... '\n' ... 'SELECT s.*\n' ... ' FROM summary s where s.rk=1 order by ' ... 's.col1,s.col2')""" >>>
Below is the desired output format of the same string —
>>> text 'with summary as select p.col1,p.col2,p.col3, ROW_NUMBER OVERPARTITION BY p.col1,p.col3 ORDER BY p.col2 AS rk from select * from select col2, col1, col3, sumcol4 as col6 from demo.tab1 a join select lpadcol5, 12, 0 as col5 from misc.tab11 ta on a.col1 = ta.col5 where col4 >= 0 and col3 like A% or col3 in complete, random, mobile group by col2, col1, col3 where col6>=1 order by col2 p SELECT s.* FROM summary s where s.rk=1 order by s.col1,s.col2'
To achieve it, one of the simplest ways I could find is using str.translate with str.maketrans —
text = text.translate(str.maketrans({"'":None,'"':None, "(":None, ")":None, "\n":None}))
Reference – https://docs.python.org/3.5/library/stdtypes.html?highlight=maketrans#str.translate