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 ProductExtract datesProduct codeSQLCloths20220612, 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_IDElectronics20220612, 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_IDBooks20220612, 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

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

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

Developer

Accepted answers

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.

Solution.yxzp

caltang

This is pretty dynamic and has the same structure:

For lumjingbki.yxmd

All comments

CoG

Here is an alternative approach using Replace() function:

Sample Workflow.yxmd

lumjingbki

@CoG 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

Hopefully that gets you a bit closer to solving this.

Solution.yxzp

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

This is pretty dynamic and has the same structure:

For lumjingbki.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335