C# - SQL WITH clause

Question

Just fishing for ideas-

I have a SQL statement that I need to parse in C#. It is formatted basically as follows -

    WITH TableX as (

    -- something else could be here..
    select field1, field2, .. fieldX
    from mytable
    -- something else could be here..

    )

    -- something else could be here..
    select /*THESE FIELDS ARE BEING REPLACED*/ TableX.field1, TableX.field2, .. TableX.fieldX
    from TableX
    -- something else could be here.. ie, more joins, selecting from subqueries. Basically another select could exist here.

Any ideas on how to replace "TableX.field1, TableX.field2, .. TableX.fieldX" with another group of field guaranteed to be in the result set?

I don't think your example is very clear yet. Could the "something else"s literally be anything (such as a complete query, followed by another `WITH`)? Do we/you have to specifically pick out the `TableX` defintion from multiple CTEs, and if so, what feature picks them out. Take a step back, and try to read your question without any knowledge of the specific problem. Which facts are absent, or would make you have to guess? — Damien_The_Unbeliever, Sep 06 '11 at 14:15
This is about string replacement. And the "-- something else could be here" should be pretty obvious. — duckmike, Sep 06 '11 at 14:20
With a "WITH" statement in SQL, the WITH defines a temporary result set that can be selected from. What I am looking for is those fields that are selected from that result set and replacing them. — duckmike, Sep 06 '11 at 14:22
Search for the line containing '/*THESE FIELDS ARE BEING REPLACED*/' then perform your replacements. ;) — PaulG, Sep 06 '11 at 14:41

score 1 · Accepted Answer · answered Sep 06 '11 at 14:13

1

you could use a complete sql parser to build a parse tree, then replace the items selectively from there.

non-trivial task.

answered Sep 06 '11 at 14:13

Randy

16,480
1
37
55

Do you think it would work to look for the first select after the closing parentheses of the definition of TableX? – duckmike Sep 06 '11 at 14:28
1

[You don't need to roll your own parser.](http://stackoverflow.com/questions/5792507/how-can-i-determine-the-parameters-required-by-an-arbitrary-piece-of-t-sql/5793088#5793088) – Martin Smith Sep 06 '11 at 14:42

C# - SQL WITH clause

1 Answers1