CODEWITHSUNDEEP
pythonregex
Pons
Pons

Reputation: 11

Python regular expression with repeating subgroup

s2="SELECT a,b,c FROM sch.Table1 a, Table4, (SELECT a, b, c FROM (select c,d FROM sch1.Table2 where x=1),Table3 where ....)"
s3="SELECT a,b,d,e FROM sch.Table1 a, sch.Table4 b, schb.Table3 WHERE 1=1"
s4="SELECT a,b,c FROM sch.table1 a,sch.table2 b WHERE colb=colc and col2=(SELECT colid FROM SCH2.TABLE3,SCH3.TABLE4 WHERE 1=1"

I have the SQL strings like the above. I'm trying to define a regular expression which would get all the Table's (sch.Table1, Table4, sch1.Table2,etc..) from the SQLs

I used the following and it returns only one table_name ['sch.Table2']

w1 = re.findall(r"(?:SELECT\s+.+\s+FROM\s)(?:(\w*\.*\w+)\s*\w?,?)",s2,re.IGNORECASE)
print w1

Thanks in advance for your help.

Upvotes: 1

Views: 117

Answers (2)

Irshad Bhat
Irshad Bhat

Reputation: 8709

Here is one simple way:

>>> s2="SELECT a,b,c FROM sch.Table1 a, Table4, (SELECT a, b, c FROM (select c,d FROM sch1.Table2 where x=1),Table3 where ....)"
>>> s3="SELECT a,b,d,e FROM sch.Table1 a, sch.Table4 b, schb.Table3 WHERE 1=1"
>>> s4="SELECT a,b,c FROM sch.table1 a,sch.table2 b WHERE colb=colc and col2=(SELECT colid FROM SCH2.TABLE3,SCH3.TABLE4 WHERE 1=1"
>>> re.findall(r"[\w\.]*Table.?",s2,re.IGNORECASE)
['sch.Table1', 'Table4', 'sch1.Table2', 'Table3']
>>> re.findall(r"[\w\.]*Table.?",s3,re.IGNORECASE)
['sch.Table1', 'sch.Table4', 'schb.Table3']
>>> re.findall(r"[\w\.]*Table.?",s4,re.IGNORECASE)
['sch.table1', 'sch.table2', 'SCH2.TABLE3', 'SCH3.TABLE4']

Upvotes: 0

Kasravnd
Kasravnd

Reputation: 107297

Instead of using regex you can use split in list comprehension :

>>> l=[s2,s3,s4]
>>> [i for s in l for i in s.split() if 'table' in i or 'Table' in i]
['sch.Table1', 'Table4,', 'sch1.Table2', 'x=1),Table3', 'sch.Table1', 'sch.Table4', 'schb.Table3', 'sch.table1', 'a,sch.table2']

or with regex:

>>> [re.findall(r'[\w\.]+Table\d|table\d',s) for s in l]
[['sch.Table1', 'sch1.Table2'], ['sch.Table1', 'sch.Table4', 'schb.Table3'], ['table1', 'table2']]

Upvotes: 1

Related Questions