How Do I Read And Parse Sql Query And Retrive Number Of Columns And Values

Question

1.00/5 (1 vote)

See more:

i want to check sql query pattern and retrieve number of columns and values from query without run it in SQL Server,Here is a example

SQL

INSERT INTO [dbo].[t]
           ([c1]
           ,[c2]
           ,[c3])
     VALUES
           (123,
           'abc',
           321)

SQL

INSERT INTO [dbo].[t]
           ([c1]
           ,[c2]
           ,[c3])
     VALUES
           (869,
           'dlk',
           893)

out put like
Name of Columns and there values

Posted 1-Dec-15 19:27pm

Jawad Ahmed Tanoli

Updated 1-Dec-15 19:53pm

v2

Add a Solution

Comments

[no name] 2-Dec-15 1:39am

c1,c2,c3 are the column names in table. Do want it in comma separated?

Jawad Ahmed Tanoli 2-Dec-15 1:46am

it is just an example there may be number of queries with different table name columns name and values.the result may be number of columns and there values does not matter including comma or not

Sergey Alexandrovich Kryukov 2-Dec-15 1:42am

Parse a query?! Are you going to develop your own SQL server?
—SA

Jawad Ahmed Tanoli 2-Dec-15 1:50am

no just want to know weather this process can be done through c# or not.As we all know SQL INSERT QUERY can be written in different ways like EXEC('YOUR QUERY') etc.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Accepted Answer · 2015-12-01T20:05:00

This can be done with a regular expression, but it might not be the best way if you have many different SQL statements you want to parse.

This solution only works for the insert statement.

C#

private static Regex insertExpr = new Regex(@"INSERT INTO\s*\[\w+\].\[\w+\]\s*\((?<columns>[\S\s]+?)\)\s* VALUES\s*\((?<values>[\S\s]+?)\)", RegexOptions.IgnoreCase);
private static Regex contentExpr = new Regex(@"(\[)?(?<val>[\d\w]+)(\])?", RegexOptions.IgnoreCase);

C#

string input = @"INSERT INTO [dbo].[t]
      ([c1]
      ,[c2]
      ,[c3])
VALUES
      (123,
      'abc',
      321)";

Match m = insertExpr.Match(input);
if (m.Success)
{
    MatchCollection cols = contentExpr.Matches(m.Groups["columns"].Value);
    MatchCollection values = contentExpr.Matches(m.Groups["values"].Value);
    
    if (cols.Count != values.Count)
       throw new Exception("Something fishy is going on.");
    
    for (int i=0; i< cols.Count; i++)
    {
       Debug.WriteLine("{0} = {1}", cols[i].Groups["val"].Value, values[i].Groups["val"].Value);
    }
}

The exact output you can modify to your liking.

The other option is to use a lexer and parser, but that is a bit more complicated to get started with.
MinosseCC: a lexer/parser generator for C#[^]
A New Parser Generator for C#[^]