How To Select Column Names And Tables Of An Sql Using Regex?

I have an SQL string that looks something like this: SELECT USER.'ID', USER.'NAME', USER.'BIRTH',USER.'GENDER', PACKAGE.'type' PACKAGE.'code' FROM 'DBNAME'.'USER' U

Solution 1:

Use this Regex will get the column and table name:

  (?is)SELECT(.*?)(?<!\w*")FROM(?!\w*?")(.*?)(?=WHERE|ORDER|$)

• Group[1] :　column
• Group[2] : table name

Code Samples:

string sql=@"SELECT
    USER.""ID"", USER.""NAME"", USER.""BIRTH"",USER.""GENDER"",
    PACKAGE.""type""
    PACKAGE.""code""
FROM
    ""DBNAME"".""USER"" USER,
    ""DBNAME2"".""PACKAGE"" PACKAGE
WHERE
    USER.""PACKAGE_ID"" = PACKAGE.""ID""
ORDER BY
    USER.""NAME"";";

    var reg=new Regex(@"(?is)SELECT(.*?)(?<!\w*"")FROM(?!\w*?"")(.*?)(?=WHERE|ORDER|$)");
    var colunms=reg.Match(sql).Groups[1].Value.Split(newchar[]{','},StringSplitOptions.RemoveEmptyEntries);
    var tables=reg.Match(sql).Groups[2].Value.Split(newchar[]{','},StringSplitOptions.RemoveEmptyEntries);