Regular Expression python sql statements

Question

I'm writing a regular expression code to extract items from a SQL statement. I want to pull out a tuple of the items after SELECT * FROM and WHERE statements.

pattern = '''
    SELECT\s\*\sFROM\s ([a-zA-Z\s*\,*\=\.]+) \sWHERE \s*([a-zA-Z\d*\s*\,*\=*\.*]+)'''

From the pattern above, I'm using it for the SQL statement below, and get the following results:

('A, B, C\n           ', 'A.ColA = B.ColB AND B.ColB2=C.ColC')

I know it's because of the huge break before the WHERE, but how do I factor that in and get rid of the huge space and /n after my first tuple?

q = """SELECT * FROM A, B, C
            WHERE A.ColA = B.ColB AND B.ColB2=C.ColC"""

Ajax1234 · Accepted Answer · 2017-12-09 19:16:06Z

1

You can loop over the returned result and use re.sub:

import re
results = ('A, B, C\n           ', 'A.ColA = B.ColB AND B.ColB2=C.ColC')
new_results = tuple(re.sub('\n\s+$', '', i) for i in results)

Output:

('A, B, C', 'A.ColA = B.ColB AND B.ColB2=C.ColC')

Or, you can alter your pattern:

import re
q = """SELECT * FROM A, B, C
        WHERE A.ColA = B.ColB AND B.ColB2=C.ColC"""
new_data = re.findall('(?<=SELECT \* FROM\s)[A-Z\s,]+(?=\n)|(?<=WHERE\s)[\w\W]+', q)

Output:

['A, B, C', 'A.ColA = B.ColB AND B.ColB2=C.ColC']

answered Dec 9, 2017 at 19:16

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GenXeral Over a year ago

Thanks! Appreciate the added approach of looping it over. And also love the cleanliness of the pattern alternation

Ajax1234 Over a year ago

@GenXeral glad to help! Please accept the answer if it helped you. Thank you!

Collectives™ on Stack Overflow

Regular Expression python sql statements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related