Replace table names occurence in SQL

Question

its not first occurence. after a index i need to get the first occurence. the question which you are pointing to is the first occurence without index

i have a sql query which i am trying to modify using String.Replace but my code is replacing where ever it finds brands, how to avoid this

string tempString = 
  "Select t0.brandid,t0.cold,t0.colm, from brands t0 where brandid=@value";

I want to replace brands which is table name between from and before t0 to tempbrands

    int tempindex1 = tempString.IndexOf("FROM ");
string tempString1 = tempString.Replace("brands", "tempbrands ");

expected result is Select t0.brandid,t0.cold,t0.colm, from tempbrands t0 where brandid=@value

its not first occurence. after a index i need to get the first occurence. the question which you are pointing to is the first occurence without index — Tan, Mar 21 '16 at 10:15
`IndexOf`has an overload to specify the start index, see this [MSDN](https://msdn.microsoft.com/en-us/library/7cct0x33(v=vs.110).aspx) — Pikoh, Mar 21 '16 at 10:20
I've reopened the question, since it's more about SQL parsing than just first substring finding. — Dmitry Bychenko, Mar 21 '16 at 10:29

Pikoh · Accepted Answer · 2016-03-21T12:14:56.467

Using the previous linked duplicate, i've modified it to specify where to start the search from. I don't know if this is what you were looking for:

string ReplaceFirst(string text, string search, string replace, string from)
{
     int posFrom= text.ToLower().IndexOf(from.ToLower());
     if (posFrom < 0)
     {
          return text;
     }
     int pos = text.ToLower().IndexOf(search.ToLower(),posFrom);
     if (pos < 0)
     {
          return text;
     }
     return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
}

Usage:

string tempString1 = ReplaceFirst(tempString, "brands", "tempbrands ", "FROM");

Edit

To replace the string between two strings you may modify the previous method like the following, but note the using just a letter as a limit is not possible. For example, i you use "t" as a limit as you ask in the comment, if the table name contains a "t" it's not going to work. You'd rather use "t0":

string ReplaceFirst(string text, string search, string replace, string from, string to)
{

    int posFrom = text.ToLower().IndexOf(from.ToLower());
    if (posFrom < 0)
    {
        return text;
    }
    int posTo = text.ToLower().IndexOf(to.ToLower(),posFrom);
    if (posTo < 0)
    {
        return text;
    }

    int pos = text.ToLower().IndexOf(search.ToLower(), posFrom);
    if (pos < 0 ||pos >posTo)
    {
        return text;
    }
    return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
}

Usage:

string tempString1 = ReplaceFirst(tempString, "brands", "tempbrands ", "FROM","t0");

can you please help me replace string between "From" and "t", that is "From tablename t0" if the search lies between "from" and "t" then only replace tablename can you please help me achieve this — Tan, Mar 21 '16 at 11:47
See updated answer, but as i say there you can't use a one letter pattern as a limit, because a match may be produced in the table name — Pikoh, Mar 21 '16 at 12:03
There are possible ways, but seems a bit dirty to me. You could set the limit in the second space after from or things like that..it depends on your prossible cases — Pikoh, Mar 21 '16 at 12:20

Dmitry Bychenko · Answer 2 · 2016-03-21T11:35:45.720

I doubt that you want to change the 1st occurence only; it's more likely that you want to rename table within SQL. e.g. for (brands => tempbrands change):

  select b1.brandId,
         b2.brandId
    from brands b1, -- both tables should be changed: this
         brands b2  -- ... and this 
   where b1.brandId > b2.brandId and
         b1.colm = b2.colm

you may mant change table names within, say, select as well

  select brands.brandId -- <- all these three occurences should be changed: this,
         brands.colm    --  ... this
    from brands         --  ... and this

A partial solution (complete solution for arbitrary query wants SQL parser) can be implemented by regular expression:

  String tempString = 
    "Select t0.brandid,t0.cold,t0.colm, from brands t0 where brandid=@value";

  // let's change just whole words "brands" into "tempbrands"
  String sql = Regex.Replace(tempString, 
                             @"\bbrands\b", 
                             match => "tempbrands",
                             RegexOptions.IgnoreCase);

Why the solution is only a partial one? SQL is reacher language than is's usually expected:

  select /*this brands should be left intact*/
         MyField as 'another brands should be spared',
    from "this strange brands table should be skipped"

i am dynamically building query based on user inputs and binding it to datagridview, so that they can export the datagridview data to excel. as the number of selected columns may change resulting in change of keyword "from" i want to find the first occurence of table name (brands here) after "from" and chagne it so my query will produce desired results. — Tan, Mar 21 '16 at 10:44

score 0 · Answer 3 · answered Mar 21 '16 at 10:36

0

Make it simply..

string tempString = 
  "Select t0.brandid,t0.cold,t0.colm, from brands t0 where brandid=@value";
tempString = tempString.Replace("from brands", "tempbrands");

answered Mar 21 '16 at 10:36

Moumit

8,314
9
55
59

the string query is not constant, it will be dynamically changing. so i need to get the first occurence after from and change it. this is for the fixed value solution – Tan Mar 21 '16 at 10:38

Replace table names occurence in SQL

3 Answers3