Regex or Regex Replace to replace STRING and DATE in SQL query

[lumjingbki]

Hi I have the below table with SQL queries and I am planning to batch run the SQL query after replacing (20220501, 20220509) and ('HYB','CCA','MON','RST') in the SQL query with "Extract dates" and "Product Code" can you please help. Thank you in advance. I am trying to use Regex replace but not able to get the code to identify or let me know if you have any other approach to follow

Product	Extract dates	Product code	SQL
Cloths	20220612, 20220613	'AAB','AAC','AAD','AAE','AAF','AAG'	SELECT AMS_code, PAMS_AQ_ID,DEM_ID SUM(ADC_CCY) AS ADC_AM FROM Table_sales_daily (NOLOCK) WHERE AMS_code IN (20220501, 20220509) AND PAMS_AQ_ID IN ('HYB','CCA','MON','RST') AND DEM_ID = 13 GROUP BY AMS_code, DEM_ID
Electronics	20220612, 20220613	'BBA','CCA'	SELECT AMS_code, PAMS_AQ_ID,DEM_ID SUM(ADC_CCY) AS ADC_AM FROM Table_sales_daily (NOLOCK) WHERE AMS_code IN (20220401, 20220406) AND PAMS_AQ_ID IN ('DUS') AND DEM_ID = 13 GROUP BY AMS_code, DEM_ID
Books	20220612, 20220613	'CCB','CCD','CCE'	SELECT AMS_code, PAMS_AQ_ID,DEM_ID SUM(ADC_CCY) AS ADC_AM FROM Table_sales_daily (NOLOCK) WHERE AMS_code IN (20220401, 20220406) AND PAMS_AQ_ID IN ('CLA','CCA','MON','RST') AND DEM_ID = 13 GROUP BY AMS_code, DEM_ID

[AndrewDMerrill]

Here is an alternative approach using Replace() function:

[lumjingbki]

@AndrewDMerrill  Thank you for the reply. I cannot hard code the "Extract Dates" and "Product code" in the replace function as they will be dynamic. I think only regex will help in this 

[rzdodson]

@lumjingbki this should be pretty close to what you are looking for.. It incorporates the Regex_Replace you are looking for. To handle the variations in the WHERE clause, I elected to use a Fina and Replace instead to give you some better functionality. Finally, you *may* need to change up the settings of the batch macro - it utilizes my ODBC connection settings I use for my Microsoft SSMS environment.

Hopefully that gets you a bit closer to solving this. :)

[lumjingbki]

@rzdodson Thank you for the reply.

Yes I can do that but then I need to maintain another input file for find and replace tool. I think regex replace when (20230506,20230507) with new date and ('AAD') with new product code will help. 

[caltang · Accepted solution]

This is pretty dynamic and has the same structure: