0

I need to split a Select clause into all its fields.

I have a Select clause like this:

SELECT c_field1 as FIELD1, ROUND(c_price * 1.075,0) as TOTALPRICE, c_id, c_sponor as SPONSORNAME

and I need a way to get something like this:

[0] c_field1 as FIELD1
[1] ROUND(c_price * 1.075,0) as TOTALPRICE
[2] c_id
[3] c_sponsor as SPONSORNAME

I have tried a lot of ways but nothig worked as I want.

If anything is unclear just let me know to clarify it. Thanks.

Spliting by ',' doesn't work because sometimes ther are select clauses like this

Select SUBSTRING(c_field1,1,8) as FIELD1, ROUND(c_price * 1.075,0) as TOTALPRICE, c_id
Yatiac
  • 1,820
  • 3
  • 15
  • 25
  • 4
    Seems like this would be a lot of work, especially once you factor in nested selects and stuff. You're basically building a SQL parser. – Mike Christensen Aug 14 '14 at 20:16
  • Do you need it just for this specific case or generic solution for any available select? – dotnetom Aug 14 '14 at 20:17
  • How general purpose does this need to be? Mike is correct, this is going to be tough if you want to be able to parse any SQL statement. If your requirements are more narrow it would be easier. – Craig W. Aug 14 '14 at 20:17
  • Seems like you could almost just get rid of the `SELECT ` at the beginning and do a `string.split` on `,`. You need more info on what you're trying to do and what you've tried so far. – tnw Aug 14 '14 at 20:18
  • @tnw - That would break on `ROUND(c_price * 1.075,0)` (Note the comma) – Mike Christensen Aug 14 '14 at 20:18
  • Why do you want to do this? Why do you need both the original name and the alias? The alias is just made up anyway. Is there a bigger issue that you are trying to solve? – Jenn Aug 14 '14 at 20:20
  • @dotnetom It need to be generic... there may be multiple cases. – Yatiac Aug 14 '14 at 20:20
  • @Jenn It can work if I can get it without the alias aswell – Yatiac Aug 14 '14 at 20:21
  • 1
    Do you have a live DB connection available against which you *could* execute this query? There is a way to get a schema without actually executing a statement. – Sergey Kalinichenko Aug 14 '14 at 20:23
  • @Yatiac I don't understand why you need to know `c_id` and the other names. If you are trying to access the values, that is not a problem, but trying to find the fields is (as everyone else has pointed out) a nontrivial problem. – Jenn Aug 14 '14 at 20:23
  • I really need help on this... Just because it is hard it is not a reason to close it... I wouldn't asked here If I haven't spend a couple of days trying to figure it out by myself. – Yatiac Aug 14 '14 at 20:23
  • @Jenn what do you mean by ;nontrivial; ? – Yatiac Aug 14 '14 at 20:24
  • There's plenty of open source SQL parsers, maybe even some written in C#. Why not study some of those? – Mike Christensen Aug 14 '14 at 20:24
  • [This post](http://stackoverflow.com/questions/589096/parsing-sql-code-in-c-sharp) might be worth checking out. Apparently there's a commercial parser that costs $12,000. – Mike Christensen Aug 14 '14 at 20:28
  • @Yatiac ... not trivial. **nontrivial**: adj. Requiring real thought or significant computing power. Often used as an understated way of saying that a problem is quite difficult or impractical, or even entirely unsolvable (“Proving P=NP is nontrivial”). The preferred emphatic form is decidedly nontrivial. [found at catb.org](http://www.catb.org/jargon/html/N/nontrivial.html) – Jenn Aug 14 '14 at 20:28
  • @Yatiac Have you seen [the source code to the Poor Man's T-SQL parser](https://github.com/TaoK/PoorMansTSqlFormatter)? It tokenizes T-SQL before formatting it. – Mike Aug 14 '14 at 20:30
  • @Jenn Got it! ty (english its not my native lang). .Mike I will take a look at it. ty. – Yatiac Aug 14 '14 at 20:31
  • @Yatiac sure, but all I had to do was google the word to get that. Anyway, my real question is what do you need this information for? Why aren't the actual results enough? – Jenn Aug 14 '14 at 20:42

1 Answers1

-3

I assume you mean that you want to split a string in C#, and that string just happens to look like an SQL SELECT clause. Does the string hve the entire SQL statement, or just the SELECT? How complicated is it?

How very simple SELECT clauses, you could just split on commas.

 var parts = selectClause.Substring(7).Split(',');

will work for the example you gave, but will fail if you have something like a function call in the select list.

James Curran
  • 101,701
  • 37
  • 181
  • 258
  • I only have the select part. There can be more commas on the select clause – Yatiac Aug 14 '14 at 20:25
  • -1, won't work for the example he gave. Please read the comments as well... it's not just for select statements – tnw Aug 14 '14 at 20:25
  • `-1` - Doesn't work for the use case provided. This has already been discussed in the comment thread. – Mike Christensen Aug 14 '14 at 20:25
  • This is very odd, since I copied the test cse directly from the question, and pasted it into LinqPad to test. It didn't have the comma in the ROUND function at that time. Moments later it did. – James Curran Aug 14 '14 at 21:20