0

I have to use regex inside C# program. Input string:

WITH Sum_OrderQuantity_CTE AS (SELECT ProductKey, EnglishMonthName, SUM(OrderQuantity) AS TotalOrdersByMonth FROM [dbo].[FactInternetSales] fs INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey GROUP BY ProductKey, EnglishMonthName ) SELECT ProductKey, AVG(TotalOrdersByMonth) AS 'Average Total Orders By Month' FROM Sum_OrderQuantity_CTE GROUP BY ProductKey ORDER BY ProductKey

I want to find strings which come after AS and is present in the parentheses. There can be multiple AS in the string. I want to find all those sentence present in the parentheses and come after AS.

I have tried some regex but failed to find correct matches:

AS\s{1,}\((.*?)\)\s{1,}(?=SELECT|UPDATE |INSERT|DELETE|AS\s{1,}\()
AS \((.*)\)
(?=AS\s{1,}\((.*)\))

bobble bubble
  • 16,888
  • 3
  • 27
  • 46
Shishank Jha
  • 27
  • 5
  • Please provide enough code so others can better understand or reproduce the problem. – Community Oct 06 '22 at 11:14
  • You need a balance group. Your input has two selects which doesn't give right rusults without a balance grouping. See : https://learn.microsoft.com/en-us/dotnet/standard/base-types/grouping-constructs-in-regular-expressions#balancing-group-definitions – jdweng Oct 06 '22 at 11:45

1 Answers1

0

This regex is the one you're looking for:

(?<=AS \()([^\(\)]*(\([^\(\)]*\))?[^\(\)]*)*(?=\))

Link: https://regex101.com/r/m9jLcs/1

Cristiano Schiaffella
  • 589
  • 2
  • 16
  • I am getting timeout issue with this regex with the input when there are sub-queries as well. [Link](https://regex101.com/r/8hca4L/1) @Cristiano – Shishank Jha Oct 18 '22 at 11:00
  • It should return 1 match in [Link](https://regex101.com/r/8hca4L/1) and match: **( Select c.*, t.CustomerId as [cust1] from dbo.CustomerATeam c Join (Select * from dbo.CustomerATeam where CustomerId in (select CustomerId from CustomerATeam)) t on c.CustomerId = t.CustomerId )** – Shishank Jha Oct 18 '22 at 11:02