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?

Answer 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#.\

http://stackoverflow.com/questions/141045/how-do-i-replace-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).

Answer 2

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?

Answer 3

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("?");
}

Answer 4

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();
} 

Answer 5

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);
    }
}