使用 M 语言提取和替换 SQL 查询中的参数 [英] extract and replace parameters in SQL query using M-language

问题描述

这个问题与这个问题有关.然而，在那个问题中我做了一些错误的假设......

This question is related with this question. However, in that question I made some wrong assumptions...

我有一个包含 SQL 查询的字符串，带有或不带有一个或多个参数，其中每个参数都有一个&"(和号)作为前缀.现在我想提取所有参数，将它们加载到 excel 表中，用户可以在其中输入每个参数的值.然后我需要使用这些值来替换 SQL 查询中的变量，以便我可以运行查询...

I have a string that contains a SQL query, with or without one or more parameters, where each parameter has a "&" (ampersand sign) as prefix. Now I want to extract all parameters, load them into a table in excel where the user can enter the values for each parameter. Then I need to use these values as a replacement for the variables in the SQL query so I can run the query...

我面临的问题是提取(因此也替换)参数名称并不是那么简单，因为参数并不总是用空格包围(正如我在上一个问题中所假设的那样)

The problem I am facing is that extracting (and therefore also replacing) the parameter names is not that straight forward, because the parameters are not always surrounded with spaces (as I assumed in my previous question)

看下面的例子

Select * from TableA where ID=&id;

Select * from TableA where (ID<&ID1 and ID>=&ID2);

Select * from TableA where ID = &id ;

所以，我的问题有两个部分:

So, two parts of my question:

1. 如何提取所有参数
2. 如何使用另一个定义了替换的表替换所有参数(另请参见 我之前的问题)

推荐答案

一个完整的解决方案需要详细了解数据的结构，并且可能会涵盖很多主题.由于您已经介绍了一种执行批量查找/替换的方法(在 Power Query 中有多种方法可以实现)，因此我将向您展示我提取参数的丑陋解决方案.

A full solution for this would require getting into details of how your data is structured and would potentially be covering a lot of topics. Since you already covered one way to do a mass find/replace (which there are a variety of ways to accomplish in Power Query), I'll just show you my ugly solution to extracting the parameters.

List.Transform
(
  List.Select
  (
   Text.Split([YOUR TEXT HERE], " "), each Text.Contains(_,"&")
  ), 
  each List.Accumulate
      (
       {";",")"}, <--- LIST OF CHARACTERS TO CLEAN
       "&" & Text.AfterDelimiter(_, "&"),
       (String,Remove) => Text.Replace(String,Remove,"")
      ) 
)

这有点令人费解，但这是我能解释的最好的情况.

This is sort of convoluted, but here's the best I can explain what is going on.

第一个关键部分是将 List.Select 与 Text.Split 结合起来，将字符串中的所有参数提取到一个列表中.它使用"来分隔列表中的单词，然后过滤到包含&"的单词，在您的第二个示例中，这意味着列表将包含(ID<&ID1"和ID>=&").ID2);"此时.

The first key part is combining List.Select with Text.Split to extract all of the parameters from the string into a list. It's using a " " to separate the words in the list, and then filtering to words containing a "&", which in your second example means the list will contain "(ID<&ID1" and "ID>=&ID2);" at this point.

第二部分是使用 Text.AfterDelimiter 提取出现在&"之后的文本在我们的参数列表中，使用 List.Accumulate 来清除"任何可能挂在参数上的不需要的字符.您要清理的字符列表必须手动定义(我只是根据示例数据输入了;"和)".我们还手动重新附加了一个&"到参数，因为 Text.AfterDelimiter 会删除它.

The second part is using Text.AfterDelimiter to extract the text that occurs after the "&" in our list of parameters, and List.Accumulate to "clean" any unwanted characters that would potentially be hanging on to the parameter. The list of characters you would want to clean has to be manually defined (I just put in ";" and ")" based on the sample data). We also manually re-append a "&" to the parameter, because Text.AfterDelimiter would have removed it.

此操作的结果是从您提供的任何示例字符串中提取参数的 List 对象.您可以设置一个获取 SQL 字符串表的查询，将此代码应用于自定义列中，其中 [YOUR TEXT HERE] 是包含您的字符串的字段，然后展开结果列表并删除其中的重复项以获得唯一列表SQL 字符串中的所有参数.

The result of this is a List object of extracted parameters from any of the sample strings you provided. You can setup a query that takes a table of your SQL strings, applies this code in a custom column where [YOUR TEXT HERE] is the field containing your strings, then expand the lists that result and remove duplicates on them to get a unique list of all the parameters in your SQL strings.

这篇关于使用 M 语言提取和替换 SQL 查询中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！