0

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/

GMB
  • 216,147
  • 25
  • 84
  • 135
triplethreat77
  • 1,276
  • 8
  • 34
  • 69
  • 1
    Honestly, even if someone (e.g. Wiktor) can give a regex which should work, if you have a long term need to do this, you might want to bite the bullet and just write a rudimentary SQL parser. At least, this parser could handle the `SELECT` portion of a SQL query. – Tim Biegeleisen Jan 02 '19 at 14:33
  • 1
    Which dbms are you using? – jarlh Jan 02 '19 at 14:38
  • It looks like you are already spitting these into 4 columns. What exactly are you trying to get here? Can you provide sample source data and what your expectations are? – Shawn Jan 02 '19 at 14:51

2 Answers2

1

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

mkayaalp
  • 2,631
  • 10
  • 17
Deep
  • 342
  • 3
  • 12
  • 1
    Is `[^select]` supposed to mean *not word "select"*? Because it doesn't. It matches any single character that is other than *"s", "e", "l", "c", "t"*. – Imantas Jan 03 '19 at 15:29
  • This will treast as First Select and a space after that. I have given link to the example for the reference. If you provide Select just before start of the any other than first line, it will accept select also and at the end of line too. – Deep Jan 03 '19 at 15:57
0

(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.

Imantas
  • 1,621
  • 13
  • 19