Help with string manipulation function

Question

I have a set of strings that contain within them one or more question marks delimited by a comma, a comma plus one or more spaces, or potentially both. So these strings are all possible:

BOB AND ?
BOB AND ?,?,?,?,?
BOB AND ?, ?, ? ,?
BOB AND ?,?  ,  ?,?
?,  ?               ,? AND BOB

I need to replace the question marks with @P#, so that the above samples would become:

BOB AND @P1
BOB AND @P1,@P2,@P3,@P4,@P5
BOB AND @P1,@P2,@P3,@P4
BOB AND @P1,@P2,@P3,@P4
@P1,@P2,@P3 AND BOB

What's the best way to do this without regex or Linq?

score 1 · Answer 1 · edited May 23 '17 at 12:19

1

If you don't want regex or LINQ, I would just write a loop, and use the "ReplaceFirst" method from this question to loop over the string, replacing each occurrence of ? with the appropriate @P#.\

How do I replace the *first instance* of a string in .NET?

Maybe something like this:

int i = 0;
while (myString.Contains("?"))
{
    myString = myString.ReplaceFirst("?", "@P" + i);
    i++;
}

Note that "ReplaceFirst" is not a standard method on string - you have to implement it (e.g. as an extension method, in this example).

edited May 23 '17 at 12:19

Community

1
1

answered May 06 '10 at 01:27

Andy White

86,444
48
176
211

LOL. I was kicking myself for having missed the `ReplaceFirst` method of `String` before I read the rest of your answer. Jeez, you want the check without doing any real work? :) – MusiGenesis May 06 '10 at 01:35

score 1 · Answer 2 · answered May 06 '10 at 01:27

1

Why not generate your SQL as you get your parameters defining proper CASE in your code and give it to execution at the very end when it is ready?

answered May 06 '10 at 01:27

Jull

271
2
5
15

The SQL was already generated for a database that allows question marks for parameters, and we have to now use it with a database that doesn't. – MusiGenesis May 06 '10 at 01:45

D'Arcy Rittich · Accepted Answer · 2010-05-06T01:41:05.320

1

I ignored the trimming of spaces in your output example, because if this is to be used in a SQL statement, the spaces are irrelevent. This should perform pretty well due to the use of StringBuilder rather than repeated calls to Replace, Substring, or other string methods.:

public static string GetParameterizedString(string s)
{
    var sb = new StringBuilder();
    var sArray = s.Split('?');
    for (var i = 0; i < sArray.Length - 1; i++)
    {
        sb.Append(sArray[i]);
        sb.Append("@P");
        sb.Append(i + 1);
    }
    sb.Append(sArray[sArray.Length - 1]);
    return sb.ToString();
}

edited May 06 '10 at 01:41

answered May 06 '10 at 01:35

D'Arcy Rittich

167,292
40
290
283

1

The spaces only affect my OCD, but mercifully I don't have to look at these queries. :) – MusiGenesis May 06 '10 at 01:49
LOL, hope my example was tidy enough. – D'Arcy Rittich May 06 '10 at 01:53
In SQL Server (which this isn't) the spaces in a query like this *could* affect the execution plan caching. Two queries that differed only by spaces would not benefit from each other's cached plans (I'm pretty sure, anyway). – MusiGenesis May 06 '10 at 01:53
@Music: That's probably true, but suggests a lack of DRY, a bigger issue IMO. – D'Arcy Rittich May 06 '10 at 01:54
My only tiniest of tiny issues would be with the second `var`, since the type returned by Split() is (arguably) not obvious. Moot point, since I'm stuck in C# 2.0. :) – MusiGenesis May 06 '10 at 01:57
I just realized that "you lack DRY" is like saying "you're all wet". :) – MusiGenesis May 06 '10 at 02:00

score 1 · Answer 4 · answered May 06 '10 at 01:51

1

If you want something out of the box :)

string toFormat = "?,  ?               ,? AND BOB";
while (toFormat.Contains("  "))
    toFormat = toFormat.Replace("  ", " ");
toFormat = toFormat.Replace("?", "{0}");
string formated = string.Format(toFormat, new PCounter());

Where PCounter is like this

class PCounter{
    int i = 0;
    public override string ToString(){
        return "@P" + (++i);
    }
}

answered May 06 '10 at 01:51

Fede

3,928
1
20
28

That's pretty cool. I didn't realize that an arg's ToString method was called anew for each placeholder. – MusiGenesis May 06 '10 at 02:12

score 0 · Answer 5 · answered May 06 '10 at 01:31

I think something like the below should do it.

string input = "BOB AND ?,?,?,?,?";
int number = 1;
int index = input.IndexOf("?");
while (index > -1)
{
    input = input.Substring(0, index).Trim() + " @P" + number++.ToString() + input.Substring(index + 1).Trim();
    index = input.IndexOf("?");
}

Help with string manipulation function

5 Answers5