0

I have sql strings such as the following, with several Trues and Falses:

INSERT INTO SystemRules (Col1, Col2, Col3, Col4, Col5) 
Values (False,False,True,False,False)

I want to replace all False with 0 and all True with 1. But when I try this:

sql = sql.Replace(",True,", ",1,").Replace(",True)", ",1)").Replace(",False,", ",0,").Replace(",False)", ",0)");

...it only removes some of the Falses...not all of them. For example, I end up with this:

INSERT INTO SystemRules (Col1, Col2, Col3, Col4, Col5) 
Values (0,False,1,0,False)

What I expected was this:

INSERT INTO SystemRules (Col1, Col2, Col3, Col4, Col5) 
Values (0,0,1,0,0)

So then I try regex instead (showing just 1 piece below):

sql = Regex.Replace(sql, ",True)", ",1)");

That particular line blows up with this error: parsing ",True)" - Too many )'s.

What is the most efficient way to replace all Trues and Falses in a sql statement with 1s and 0s? I have always found string.Replace() in c# to replace globally, I'm baffled as to why it's missing some. There are no spaces in there, I have triple-checked. One proof of this is that if I run the series of REPLACEs above a 2nd time, it does replace the stragglers. Why not the first time? Is c# replace really not global? Thank you.

HerrimanCoder
  • 6,835
  • 24
  • 78
  • 158
  • 1
    Why not do it during the SQL building `col1Value?1:0` or `col1Value == "True"?1:0` – ASpirin Aug 14 '17 at 17:34
  • 3
    I don't know how it replaced the first `False` with `0` even, since none of your `.Replace()` arguments allows for `False` without a prepended comma. – itsme86 Aug 14 '17 at 17:35
  • 2
    An attempt to reproduce your issue does not produce the result you describe. Your description of the problem is not accurate: http://ideone.com/FzUE6c – JLRishe Aug 14 '17 at 17:37
  • 1
    Your repro is invalid. Also, you're accounting for when the word is inbetween commas, or is at the end and only has a preceding comma, but you aren't taking into account when the word is at the start, and only has a receding comma. Also, why bother with the bloody commas at all? –  Aug 14 '17 at 17:39
  • How are you generating this string in the first place? Couldn't you replace the 0's and 1's at generation time rather than afterwards? Or even better use a parameterized SQL query. – Dax Fohl Aug 14 '17 at 17:48

2 Answers2

3

You get the error with Regex.Replace because ) is a metacharacter that needs to be escaped. Placing a backspace (two backspaces for regular C# string) will address this problem:

sql = Regex.Replace(sql, ",True\\)", ",1\\)");

You can simplify comma/parentheses handling with \\b anchor:

sql = Regex.Replace(sql, "\\bTrue\\b", "1");

However, manipulating SQL as a string is a very dangerous idea (why?). You should refactor your code to use parameterized SQL instead (how?).

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
0

There is no need to put Regex or ",True," It can be done in a very simple way 

 string s="INSERT INTO SystemRules (Col1, Col2, Col3, Col4, Col5)Values (False,False,True,False,False)";
        s=s.Replace("False", "0").Replace("True", "1");
Vivek Shah
  • 55
  • 8