CODEWITHSUNDEEP
c#sql.netregexsplit
triplethreat77
triplethreat77

Reputation: 1296

Regex split by commas - not within parenthesis or single quotes

I have the following SQL statement that I'm attempting to split into columns: 

select 
    count(rtnotes.keyno) as value,
    ent.company as label, 
    'j-ChartDrillDown-406,'+CAST(ENT.ENTID AS CHAR(10)) AS link, 
    dateadd(week, datediff(wk, 0, dateadd(wk,-5,getdate())), -1) as test
    ...

I want this split into the 4 columns within the select statement:

  1. count(rtnotes.keyno) as value
  2. ent.company as label
  3. 'j-ChartDrillDown-406,'+CAST(ENT.ENTID AS CHAR(10)) AS link
  4. dateadd(week, datediff(wk, 0, dateadd(wk,-5,getdate())), -1) as test

I have been able to split by commas not within quotes using:

,(?=(?:[^']*'[^']*')*[^']*$)

but I also need to ignore commas within parenthesis (nested parenthesis in this case due to dateadd and datediff)

https://regex101.com/r/UUNUF9/1/

Upvotes: 0

Views: 196

Answers (2)

Deep
Deep

Reputation: 382

Here is the regex you will need.

Regex:

[^select][^\s].+[\(\)]?[^,|\s]

For example with your data, follow the link. https://regex101.com/r/Zhk1JP/2

Upvotes: 1

Imantas
Imantas

Reputation: 1662

(too long for comment)

Unless you know the exact number of levels of nested parenthesis for every query you will parse, you can't. Basically because it becomes the same as parsing HTML using Regular Expressions.

The reason is that parenthesis in such case are like opening and closing tags of HTML. Moreover, you have to make sure you handle cases like SELECT ':)' AS Smiley or CAST(')' AS NCHAR(1)) which can have parenthesis enclosed in string values and that will cause you a lot of headache.

However, there are projects like SQL Parser which might help you achieve what you want.

Upvotes: 0

Related Questions