Regex to match following pattern in SQL query

Question

I am trying to extract parts of a MySQL query to get the information I want.

I used this code / regex in Python:

import re
query = "SELECT `asd`.`ssss` as `column1`, `ss`.`wwwwwww` from `table`"
table_and_columns = re.findall('\`.*?`[.]\`.*?`',query)

My expected output:

['`asd`.`ssss`', `ss`.`wwwwwww`']

My real output:

['`asd`.`ssss`', '`column1`, `ss`.`wwwwwww`']

Can anybody help me and explain me where I went wrong? The regex should only find the ones that have two strings like asd and a dot in the middle.

PS: I know that this is not a valid query.

The fourth bird · Accepted Answer · 2020-09-06 19:39:07Z

1

The dot . can also match a backtick, so the pattern starts by matching a backtick and is able to match all chars until it reaches the literal dot in [.]

There is no need to use non greedy quantifiers, you can use a negated character class only prevent crossing the backtick boundary.

`[^`]*`\.`[^`]*`

Regex demo

The asterix * matches 0 or more times. If there has to be at least a single char, and newlines and spaces are unwanted, you could add \s to prevent matching whitespace chars and use + to match 1 or more times.

`[^`\s]+`\.`[^`\s]+`

Regex demo | Python demo

For example

import re
query = "SELECT `asd`.`ssss` as `column1`, `ss`.`wwwwwww` from `table`"
table_and_columns = re.findall('`[^`\s]+`\.`[^`\s]+`',query)
print(table_and_columns)

Output

['`asd`.`ssss`', '`ss`.`wwwwwww`']

edited Sep 6, 2020 at 19:39

answered Sep 6, 2020 at 15:06

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

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

Comments

Liju · Accepted Answer · 2020-09-06 14:14:34Z

0

Please try below regex. Greedy nature of .* from left to right is what caused issue.
Instead you should search for [^`]*

`[^`]*?`\.`[^`]*?`

Demo

answered Sep 6, 2020 at 14:14

Liju

2,3133 gold badges8 silver badges21 bronze badges

Comments

Anna Slastnikova · Accepted Answer · 2020-09-06 14:18:39Z

0

The thing is that .*? matches any character (except for line terminators) even whitespaces. Also as you're already using * which means either 0 or unlimited occurrences,not sure you need to use ?. So this seems to work:

\`\S+\`[.]\`\S+\`

where \S is any non-whitespace character. You always can check you regexes using https://regex101.com

answered Sep 6, 2020 at 14:18

Anna Slastnikova

1,61815 silver badges11 bronze badges

Collectives™ on Stack Overflow

Regex to match following pattern in SQL query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related