CODEWITHSUNDEEP
c#regex
joce
joce

Reputation: 9892

Regex to extract data on some lines while discarding others

I have these SQL DB creation scripts that I want to extract table names from to create GRANT accesses to.

To extract the actual names isn't a problem:

^CREATE TABLE ([\w\.]+)[\r\n]+

However, if I want to just do a Regex.Replace to create the GRANT statements out of these names (in group 1), I'm stuck with all the lines that don't match that I'd like to get rid off.

e.g.

For:

CREATE TABLE dbo.t_MyType
(
    ID INTEGER PRIMARY KEY IDENTITY,
    TypeName VARCHAR(16) NOT NULL
)
CREATE UNIQUE INDEX IndexMyType ON dbo.t_MyType (TypeName)

CREATE TABLE dbo.t_MyType2
(
    ID INTEGER PRIMARY KEY IDENTITY,
    TypeName VARCHAR(16) NOT NULL
)
CREATE UNIQUE INDEX IndexMyType ON dbo.t_MyType2 (TypeName)

I get:

GRANT SELECT ON dbo.t_MyType TO db_read(
    ID INTEGER PRIMARY KEY IDENTITY,
    TypeName VARCHAR(16) NOT NULL
)
CREATE UNIQUE INDEX IndexMyType ON dbo.t_MyType (TypeName)

GRANT SELECT ON dbo.t_MyType2 TO db_read(
    ID INTEGER PRIMARY KEY IDENTITY,
    TypeName VARCHAR(16) NOT NULL
)
CREATE UNIQUE INDEX IndexMyType ON dbo.t_MyType2 (TypeName)

So the question is: What are the regex and replace string pair I need to use in order to get the following result?

GRANT SELECT ON dbo.t_MyType TO db_read
GRANT SELECT ON dbo.t_MyType2 TO db_read

Upvotes: 1

Views: 113

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127563

Don't do a replace, just enumerate through the MatchCollection returned by Regex.Matches and build a new sql file. (You will need to modify your regex a bit)

static void Main(string[] args)
{
    string query = 
@"CREATE TABLE dbo.t_MyType
(
    ID INTEGER PRIMARY KEY IDENTITY,
    TypeName VARCHAR(16) NOT NULL
)
CREATE UNIQUE INDEX IndexMyType ON dbo.t_MyType (TypeName)

CREATE TABLE dbo.t_MyType2
(
    ID INTEGER PRIMARY KEY IDENTITY,
    TypeName VARCHAR(16) NOT NULL
)
CREATE UNIQUE INDEX IndexMyType ON dbo.t_MyType2 (TypeName)";

    string matchString = @"CREATE TABLE ([\w\.]+)";


    var matches = Regex.Matches(query, matchString);

    StringBuilder sb = new StringBuilder();

    foreach (Match match in matches)
    {
        sb.AppendFormat("GRANT SELECT ON {0} TO db_read", match.Groups[1]).AppendLine();
    }

    Console.WriteLine(sb.ToString());

    Console.ReadLine();

}

Upvotes: 1

Related Questions