Regex Lookahead and Lookbehind to parse SQL statement

Question

I am trying to parse SQL statements with regex and save it's parameters to use later.

Lets say I have this SQL statement:

INSERT INTO tablename (id, name, email) VALUES (@id, @name, @email)

The following regex will work just fine:

(@[0-9a-zA-Z$_]+)

However in this statement I should ignore everything in ' ' or " " and save only first parameter:

 UPDATE mytable SET id = @id, name = 'myname@id' WHERE id = 1;

According to this answer https://stackoverflow.com/a/307957 "it's not practical to do it in a single regular expression", but I am still trying to do this.

I tried to add Regex Lookahead and Lookbehind, but its not working:

(?<!\').*(@[0-9a-zA-Z$_]+).*(?!\')

Is there any way to do it using only one regular expression? Should I use lookahead/lookbehind or something else?

Regex isn't the way to go. Look at this question: http://stackoverflow.com/questions/660609/sql-parser-library-for-java — Casimir et Hippolyte, Sep 15 '16 at 13:25
@CasimiretHippolyte yea, thats true. But for educational purposes why not use regex?) — TotallyStuck, Sep 15 '16 at 13:51

Taisbevalle · Answer 1 · 2016-09-15T13:29:02.880

You can use: [\=$\s]\s*\@[0-9+^a-zA-Z_0-9$_]+\s*[$,]

Explanation:

[\=\(\s] match a single character present in the list below

\= matches the character = literally
\( matches the character ( literally
\s match any white space character [\r\n\t\f ]

\s* match any white space character [\r\n\t\f ]

Quantifier: * Between zero and unlimited times, as many times as possible, giving back as needed [greedy]
\@ matches the character @ literally [0-9+^a-zA-Z_0-9$_]+ match a single character present in the list below
Quantifier: + Between one and unlimited times, as many times as possible, giving back as needed [greedy]
0-9 a single character in the range between 0 and 9
+^ a single character in the list +^ literally
a-z a single character in the range between a and z (case insensitive)
A-Z a single character in the range between A and Z (case insensitive)
_ the literal character _
0-9 a single character in the range between 0 and 9
$_ a single character in the list $_ literally

\s* match any white space character [\r\n\t\f ]

Quantifier: * Between zero and unlimited times, as many times as possible, giving back as needed [greedy]

[\),] match a single character present in the list below

\) matches the character ) literally
, the literal character ,

Your answer works perfectly, thank you very much, I will use it too. — TotallyStuck, Sep 15 '16 at 18:44

Nikolas Charalambidis · Accepted Answer · 2016-09-15T13:42:23.473

0

You can simplify your regex. Note the group you want always to capture is followed with , or ). Being aware of this fact you get this regex:

(@[0-9a-zA-Z$_]+)(?=[,)])

@[0-9a-zA-Z$_]+ is your value
(?=[,)]) checks if the ) or , character follows.

If the way describing where your string can't be placed is complicated, better look where it must be places instead.

See how it works at Regex101.

edited Sep 15 '16 at 13:42

answered Sep 15 '16 at 13:32

Nikolas Charalambidis

40,893
16
117
183

Yea, if string has ) or , like name = 'myname@id,something' then it will pass the regex – TotallyStuck Sep 15 '16 at 14:23
According your input, it works. If you want the solution with a wider coverage, please provide more sample inputs. – Nikolas Charalambidis Sep 15 '16 at 16:05
I think it's enough. You mentioned Lookahead, so I mark the question as answered, thank you! If you want to look for another example: https://regex101.com/r/nW7lZ9/3 – TotallyStuck Sep 15 '16 at 18:25

Regex Lookahead and Lookbehind to parse SQL statement

2 Answers2