Get parameter names from SQL Query

Question

The backend is a PostgreSQL 9.1 server.

I am trying to create AdHoc XML reports. Report files will contain SQL queries, all of which must begin with a SELECT statement. SQL queries will have parameters. Depending on the data type of the respective columns, these parameters will be accordingly presented to the user to provide values ​​(values).

Unprocessed SQL query:

SELECT * FROM customers WHERE ( customers.customer_code=@customer _code AND customers.location=@location AND customers.type= ( SELECT type from types WHERE types.code=@type _code AND types.is_active = @type_is_active ) AND customers.account_open_date BETWEEN @start_date AND @end_date ) OR customers.flagged = @flagged; 

I want to get a list of column names and parameters from a query string and put them in an array of strings and process them later.

I can only match parameters using the following regular expression:

 @(?)(?<parameter>\w+) 

Expected Matches:

 customers.customer_code=@customer _code customers.location=@location types.code=@type _code types.is_active = @type_is_active customers.account_open_date BETWEEN @start_date AND @end_date customers.flagged = @flagged 

How to match "@Parameter", "=" and "BETWEEN" at once?

Answer 1

I know this a little late, but for future research:

I think this Regex serves your purpose:

 (\w+\.\w+(?:\s?\=\s?\@\w+|\sBETWEEN\s\@\w+\sAND\s\@\w+)) 

Check this Regex101 script here and carefully read the explanation of each part.

Basically, he first looks for customer.xxx_yyy columns, and then either for = @variable or BETWEEN @variable1 AND @variable2 .

Captured groups:

 MATCH 1 1. [37-75] ` customers.customer_code=@customer _code` MATCH 2 1. [80-108] ` customers.location=@location ` MATCH 3 1. [184-205] ` types.code=@type _code` MATCH 4 1. [218-251] `types.is_active = @type_is_active` MATCH 5 1. [266-327] `customers.account_open_date BETWEEN @start_date AND @end_date` MATCH 6 1. [333-361] `customers.flagged = @flagged`