获得SQL查询参数名称 [英] Get Parameter Names from SQL Query

问题描述

后端是PostgreSQL服务器9.1。

The backend is PostgreSQL server 9.1.

我想建立的AdHoc XML报告。报告文件将包含SQL查询，所有这一切都必须以SELECT语句开始。在SQL查询将有参数。根据相关列的数据类型，这些参数将相应$ psented向用户提供值（S）芘$

I am trying to build AdHoc XML reports. The report files will contain SQL queries, all of which must start with a SELECT statement. The SQL queries will have parameters. Depending upon the data type of the associated columns, these parameters will be accordingly presented to the user to provide value(s).

一个rought SQL查询：

A rought 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 list of the column names and parameters from the query string and put them into a string array and process later.

我能够只匹配使用下面的正前pression的参数：

I am able to match only the parameters using the following regular expression:

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

预计匹配：

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

如何匹配@Parameter，=和之间的时候了？

推荐答案

的我知道这是一个有点晚，但对未来的研究的缘故：的

我想这正则表达式提供你的目的：

I think this Regex serves your purpose:

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

这Regex101小提琴这里，并仔细阅读它的每个部分的解释。

Check this Regex101 fiddle here, and read carefully the explanation for each part of it.

基本上，它首先查找你的 customer.xxx_yyy 列，然后或者 = @variable 或 @之间变量1 AND @变量2 。

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

捕获组：

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`

这篇关于获得SQL查询参数名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！