extract and replace parameters in SQL query using

Question

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

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)

See following examples

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. How can I extract all parameters
2. How can I replace all parameters using another table where the replacements are defined (see also my previous question)

Answer 1

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.

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.

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.

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.