Regex or Regex Replace to replace STRING and DATE in SQL query
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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. :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
