Finding the index of an external FROM clause in a SQL statement string

Question

I need to find the index of the actual external 'from' in the SQL string. Here are some examples of SQL strings. I would look for the actual OT:

select * from table --easy  
select *, (select top 1 col1 from anothertable) as col1 from table
select * from table,  (select col1 from anothertable) as anothertable 
select * from table where col1=(select top 1 col1 from anothertable) 

I am sure there are many more valid SQL statements that use subselects. I think I need a regular expression or parser that knows the difference between the outermost “from” and skips any subsets. Of course, I may not consider other pitfalls to find an external “off”, so any input will be appreciated.

Answer 1

, "", , - , (+1 , -1 ). "", 0, .

Answer 2

Microsoft.Data.Schema.ScriptDom . .

using System;
using System.Collections.Generic;
using System.IO;
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;

namespace ConsoleApplication1
{
    internal class Program
    {
        public static void Main(string[] args)
        {
            string[] queries =
                {
                    @"select * from table1;",
                    /*Test multiline*/
                    @"select *, 
                              (select top 1 col1 from anothertable) as col1 from table1;"
                    ,
                    @"select * from table1,  (select col1 from anothertable) as anothertable;",
                    @"select * from table1 where col1=(select top 1 col1 from anothertable)",
                    /*Test invalid syntax ("table" is a reserved word)*/
                    @"select * from table where col1=(select top 1 col1 from anothertable)"
                };

            foreach (string query in queries)
            {
                Parse(query);
            }

            Console.ReadKey();
        }

        private static void Parse(string query)
        {
            Console.WriteLine(@"------------------------------------------");
            Console.WriteLine(@"Parsing statement ""{0}""", query);

            var parser = new TSql100Parser(false);

            IList<ParseError> errors;
            IScriptFragment result = parser.Parse(new StringReader(query), out errors);

            if (errors.Count > 0)
            {
                Console.WriteLine(@"Errors encountered: ""{0}""", errors[0].Message);
                return;
            }


            TSqlStatement statement = ((TSqlScript) result).Batches[0].Statements[0];

            if (statement is SelectStatement)
            {
                TableSource tableSource = (((QuerySpecification)((SelectStatement)statement).QueryExpression).FromClauses[0]);

                Console.WriteLine(@"Top level FROM clause at Line {0}, Column {1} (Character Offset {2})",
                                  tableSource.StartLine, tableSource.StartColumn, tableSource.StartOffset);
                Console.WriteLine();
                Console.WriteLine();

            }
        }
    }
}

------------------------------------------
Parsing statement "select * from table1;"
Top level FROM clause at Line 1, Column 15 (Character Offset 14)


------------------------------------------
Parsing statement "select *,
                              (select top 1 col1 from anothertable) as col1 from
 table1;"
Top level FROM clause at Line 2, Column 82 (Character Offset 93)


------------------------------------------
Parsing statement "select * from table1,  (select col1 from anothertable) as ano
thertable;"
Top level FROM clause at Line 1, Column 15 (Character Offset 14)


------------------------------------------
Parsing statement "select * from table1 where col1=(select top 1 col1 from anoth
ertable)"
Top level FROM clause at Line 1, Column 15 (Character Offset 14)


------------------------------------------
Parsing statement "select * from table where col1=(select top 1 col1 from anothe
rtable)"
Errors encountered: "Incorrect syntax near table."

Answer 3

Note that from can occur in a select clause as a field name, alias, or SP name. For example, a selection from a value from TABLE is valid if TABLE has a field.