CODEWITHSUNDEEP
c#regex
VeecoTech
VeecoTech

Reputation: 2143

Match the string for different string formating

I have a random .sql file with long sql query. I need to runtime get all table names from the insert sql statement to put into an array. I am able to get the table names (Inventory.tableA) out if the insert statement is in (one line) like the format below:

...
Insert into Inventory.tableA;
...

However, if the Insert statement is in multiple lines like below e.g. 

Insert into 
Inventory.tableA;

OR

Insert into 
(blank line)
(blank line)
Inventory.tableA;

Then my query to retrieve the table name will fail. Can you advise how i can get the table name out from the long sql query which the insert statement can be in either one line or multiple lines? What is the best approach here?

Below is the c# query i tried which can handle for 1 line.

public List<string> GetAllTablesNames(string sp)
        {
            List<string> output = new List<string>();

            string[] contentSplit = sp.Split(new string[] { "INSERT INTO " }, StringSplitOptions.None);
            for (int a = 1; a < contentSplit.Length; a++)
            {
                string[] sa_tableName = contentSplit[a].Substring(0, contentSplit[a].IndexOf("\r")).Trim().Split('.');

                    output.Add(sa_tableName[0] + "." + sa_tableName[1]);
            }

            return output.Distinct().ToList();
        }

Upvotes: 0

Views: 95

Answers (3)

ΩmegaMan
ΩmegaMan

Reputation: 31576

\s will ignore all whitespace, so by using \s+ it will skip over tabs & CRLF. Since your examples had ;, we will capture all text that is not ;. If there is a space between, then use [^;\s]+ instead. 

string text = @"Insert into  
    Inventory.tableA;
    Insert into Orders;";

    var tableNames = Regex.Matches(text, @"(?:Insert into\s+)(?<Name>[^;]+)(?:;)")
                          .OfType<Match>()
                          .Select (mt => mt.Groups["Name"].Value);

    Console.WriteLine ("Tables: {0}", string.Join(" ", tableNames));            

    /* Tables: Inventory.tableA Orders */

Upvotes: 1

Anirudha
Anirudha

Reputation: 32787

Use singleline mode

List<string> tables= Regex.Matches("yourInput",@"Insert into\s+(.*?)[\s\(]+"
,RegexOptions.Singleline|RegexOptions.IgnoreCase)
.Cast<Match>().Select(x=>x.Groups[1].Value)
.ToList<string>();

//tables contains all the table names

Upvotes: 2

lc.
lc.

Reputation: 116438

I'm not sure what your regex is, but you could:

  1. Change all spaces in your regex to \s+.
  2. Replace all endlines with " " first, then run it through your regex.

Upvotes: 1

Related Questions