[FIXED] Get list of parameter names from native sql expression (regex)

Issue

I'm having trouble getting list of all parameters in SQL query using Regex.

Example of the query:

SELECT ... WHERE col1 = :user AND col2 = 'HELLO' OR col3 = :language

To obtain parameters, I use following regex pattern:

Pattern.compile(":([\\w.$]+|\"[^\"]+\"|'[^']+')", Pattern.MULTILINE)

The pattern returns list of parameters correctly:

:user
:language

The problem is with another type of query, where literals might contain character ':'

WHERE col1 = :user AND some_date > '2022-09-26T10:22:55'

The list of parameters for this case is:

:user
:22
:55

Is there any better approach that will not consider contents of literals as parameters?

Solution

You could simplify your problem by assuming that a named param in sql is just a word with prefix : and always follows after a space (this is actually not a requirement or always true but might be just good enough to get you acceptable results with as simple of regex as possible)

Pattern.compile(" :\\w+", Pattern.MULTILINE)

--

summary of the comments:

had to match 
- foo = :param AND :param = bar AND foo=:param AND :param=bar
- AND FUNC(:param) OR FUNC(0, :param) OR FUNC(:param, 0)

finally this regex with fixed length lookahead and variable length lookbehind was helpful:

 Pattern.compile("(?<=[=(])\\s*:[\\w_.]+|:[\\w_.]+(?=\s*[=)])", Pattern.MULTILINE)

Answered By - bw_üezi
Answer Checked By - David Marino (JavaFixing Volunteer)