Replace part of a string between the x and x+1 occurrence c#

Question

My code.

string sql = "INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (, NULL, NULL, NULL, NULL, NULL,)";
//Insert value for the third column(between the 3rd and 4th comma)
Regex rgx = new Regex(@", null"); 
sql = rgx.Replace(sql, ", 'abc'", 3);// this doesn't work
sql = rgx.Replace(sql, ", 'def'", 4);// second insert

Desired result

sql = "INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (, NULL, NULL, 'abc', 'def', NULL,)";
//Then I'll remove the first and last comma between the VALUES parenthesis.

_"//Then I'll remove the first and last comma"_ Is this part of this or your next question? — Tim Schmelter, Oct 01 '13 at 07:39
What is the result that you Actually get from that? And at first glance I don't think you can use RegEx in that way (although my knowledge of them is limited) — Nunners, Oct 01 '13 at 07:39

Soner Gönül · Answer 1 · 2013-10-01T07:50:23.273

1

Use like this;

string sql = "INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (,NULL, NULL, NULL, NULL, NULL,)";

string nulls = "NULL";
List<int> indexes = new List<int>();
foreach (Match match in Regex.Matches(sql, nulls))
{
     indexes.Add(match.Index);
}

sql = sql.Remove(indexes[2], 4).Insert(indexes[2], "'abc'");
Console.WriteLine(sql);

Output will be;

INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (,NULL, NULL, 'abc', NULL
, NULL,)

As an explanation, this will find third NULL in your query, then replace itself with 'abc'.

Here a DEMO.

edited Oct 01 '13 at 07:50

answered Oct 01 '13 at 07:41

Soner Gönül

97,193
102
206
364

Good example, BUT... what happens when I want to make the second insert ? After the first insert, the list of indexes don't correspond anymore. – Misi Oct 01 '13 at 09:13
@Misi You can change indexes in line `sql = sql.Remove(indexes[2], 4).Insert(indexes[2], "'abc'");` But remember, when you insert first `'abc'` indexes available from `0` to `3` since you have 4 `NULL` instead of 5 in your sql. – Soner Gönül Oct 01 '13 at 10:11

score 1 · Answer 2 · edited May 23 '17 at 10:25

You can use this extension method. A modified version of this.

public static string ReplaceSpecifiedIndex(this string input, string valueToBeReplaced, string replacingvalue, int index)
  {
            input = input.ToLower();
            valueToBeReplaced = valueToBeReplaced.ToLower();
            replacingvalue = replacingvalue.ToLower();
            Match m = Regex.Match(input, "((" + valueToBeReplaced + ").*?){" + index + "}");
            int specificIndex = -1;
            if (m.Success)
                specificIndex = m.Groups[2].Captures[index - 1].Index;

     if (specificIndex > -1)
     {
                string temp = input.Substring(specificIndex, valueToBeReplaced.Length);
                int nextsubstring = specificIndex + valueToBeReplaced.Length;
                input = input.Substring(0, specificIndex) + temp.Replace(valueToBeReplaced, replacingvalue) + input.Substring(nextsubstring);
      }
      return input;
  }

and call it like this

string sql = "INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (, NULL, NULL, NULL, NULL, NULL,)";
sql = sql.ReplaceSpecifiedIndex("null", "abc", 3);

Tim Schmelter · Answer 3 · 2013-10-01T08:28:13.597

Without regex:

string sql = "INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (, NULL, NULL, NULL, NULL, NULL,)";
int indexOfvalues = sql.IndexOf("VALUES (");
if (indexOfvalues >= 0)
{
    indexOfvalues += "VALUES (".Length;
    int endIndexOfvalues = sql.IndexOf(")", indexOfvalues);
    if (endIndexOfvalues >= 0)
    {
        string sqlValues = sql.Substring(indexOfvalues, endIndexOfvalues - indexOfvalues);
        string[] values = sqlValues.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        if(values.Length >= 3)
            values[2] = "'abc'";
        string newValues = string.Join(",", values);
        sql = string.Format("{0}{1})", sql.Substring(0, indexOfvalues), newValues.Trim());
    }
}

Result:

INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (NULL, NULL, 'abc', NULL, NULL)

or with String.Split shorter and more readable (possibly a little bit more dangerous):

string sql = "INSERT INTO TABLE1(col1, col2, col3, col4, col5) VALUES (, NULL, NULL, NULL, NULL, NULL,)";
string[] tokens = sql.Split(new[] { "VALUES" }, StringSplitOptions.None);
if (tokens.Length == 2)
{
    string[] values = tokens[1].Trim('(', ')', ',', ' ').Split(',');
    if (values.Length >= 3)
        values[2] = "'abc'";
    string newValues = string.Join(",", values);
    sql = string.Format("{0} VALUES ({1})", tokens[0], newValues);
}

// same result

score 0 · Answer 4 · edited May 23 '17 at 12:29

0

MatchEvaluator is simply a delegate. You pass in your function.

https://stackoverflow.com/a/4566891/431471

edited May 23 '17 at 12:29

Community

1
1

answered Oct 01 '13 at 07:40

Kendo

41
1
5

score 0 · Answer 5 · edited May 23 '17 at 12:13

0

You really could use the regular expression /((s).*?){n}/ to search for n-th occurrence of substring s.

And regarding //Then I'll remove the first and last comma.

sql = sql.Replace("(,", "(").Replace(",)", ")");

edited May 23 '17 at 12:13

Community

1
1

answered Oct 01 '13 at 07:43

Sadique

22,572
7
65
91

score 0 · Answer 6 · answered Oct 01 '13 at 12:23

I've edited the StrExtract function from the the VFP Toolkit for .NET and got a new function that I named StrReplace.

public static string StrReplace(string cSearchExpression, string replacement, string cBeginDelim, string cEndDelim, int nBeginOccurence)
    {
        string cstring = cSearchExpression;
        string cb = cBeginDelim;
        string ce = cEndDelim;

        if (cSearchExpression.Contains(cBeginDelim) == false && cSearchExpression.Contains(cEndDelim) == false)
        {
            return cstring;
        }

        //Lookup the position in the string
        int nbpos = At(cb, cstring, nBeginOccurence) + cb.Length - 1;
        int nepos = cstring.IndexOf(ce, nbpos + 1);

        //Reaplce the part of the string if we get it right
        if (nepos > nbpos)
        {
            cstring = cstring.Remove(nbpos, nepos - nbpos).Insert(nbpos, replacement);
        }
        return cstring;
    }

At function can be found in the toolkit.

sql = strings.StrReplace(sql , " abc", ",", ",", 3);
sql = strings.StrReplace(sql , " def", ",", ",", 4);

Replace part of a string between the x and x+1 occurrence c#

6 Answers6