-1

I'm trying to turn this string:

INSERT INTO [main].[sqlite_default_schema].[TableName] ([SomeColumn], [SomeOtherColumn], [Last]) VALUES (@param1, @param2, @param3);

into this one:

INSERT INTO [main].[sqlite_default_schema].[TableName] ([SomeColumn], [SomeOtherColumn], [Last]) VALUES (@SomeColumn, @SomeOtherColumn, @Last);

As a beginner in regex, i'm using this C# snippet:

Regex regex = new Regex(@"\(.*?\)");
MatchCollection matches = regex.Matches(commandText);
if (matches[0].Success && matches[1].Success)
{
    Regex reColNames = new Regex(@"\[\w*\]");
    MatchCollection colNames = reColNames.Matches(matches[0].Value);
    Regex reParamNames = new Regex(@"\@\w*");
    MatchCollection paramNames = reParamNames.Matches(matches[1].Value);
    if (colNames.Count > 0 && colNames.Count == paramNames.Count)
    {
        for (int i = 0; i < paramNames.Count; i++)
        {
            string colName = colNames[i].Value.Substring(1, colNames[i].Length - 2);
            commandText = commandText.Replace(paramNames[i].Value, "@" + colName);
        }
    }
}
return commandText;

This works but doesn't feel right. Is there a way to achieve the same result by using just one regex?

Cheers!

BigFish
  • 21
  • 7
  • 1
    Does this answer your question? [Regex replace all occurences](https://stackoverflow.com/questions/17242416/regex-replace-all-occurrences) – Rafalon Feb 14 '20 at 10:26
  • Imo, the solution requires 3 steps: 1 -> finding everything within () 2 -> extracting colNames from the first group found in 1. 3 -> replace params with corresponding colNames based on index in second group. – BigFish Feb 14 '20 at 10:39
  • How many such queries exist? It would be far easier if you created separated queries as constant strings, or switched to an ORM. – Tanveer Badar Feb 14 '20 at 11:41
  • You have a CSV string : "@Column1, @Column2, @Column3". So use : string csv = string.Join(",",Enumerable.Range(1,3).Select(x => "@Column" + x.ToString())); – jdweng Feb 14 '20 at 11:48
  • @TanveerBadar This is part of an attempt to automate the creation of Statement Constants. – BigFish Feb 14 '20 at 11:58

1 Answers1

0

It seems to me you are replacing all "param" text with "column". Why not start with that in the first place? If that's impossible due to some concerns you haven't mentioned see the solution below.

This will bulk replace everything in 1 line.

Regex.Replace(query, "param", "column", RegexOptions.None);
Tanveer Badar
  • 5,438
  • 2
  • 27
  • 32
  • While true for the params, I cannot be certain of column names, as they actually represent the columns of a Table in the DB. So [Column1], [Column2], ... would be actual Names like [FirstName], [LastName], ... – BigFish Feb 14 '20 at 11:52
  • Then please update the question with a better example. As pattern matching goes, that's not currently evident. I will leave this answer up for posterity. – Tanveer Badar Feb 14 '20 at 11:54