Replace Apostrophe with Double Apostrophe

[LLDLLD]

I have tried using the simple Replace function in C# to find and replace a single apostrophe with two, but neither the Replace function nor the IndexOf function find the single apostrophe.

Example: strToReplace = "Joe's shoes"

-- Code: strToReplace = strToReplace.Replace("'", "''");

Result I want: "Joe''s shoes" -- in order to use in a SQL insert later.

 

[Rhys666]

Don't know what dB you're using but guessing MS Sql Server. If you use a stored procedure to insert rather than a Sql String, and pass Joe's Shoes as an input parameter, the single apostrphe is handled appropriately and is not a problem. There are multiple other reasons but I won't go into them.

Other than that, the replace function does work as you're expecting it, (I even did a quick test as its darned early ), so in debug check the value of the strToReplace variable prior to calling the Replace() method to ensure it is what you're expecting. Can you provide a copy of the exact code not working for you?

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[stevexff]

Can you PREPARE a SQL statement with placeholders, e.g.

SELECT blah FROM T1 WHERE x = ? AND y = ?";

and just pass the parameters in when you execute the query. This normally handles quoting issues quite nicely and has the benefit of protecting you from SQL injection attacks.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[LLDLLD]

I appreciate the feedback. Perhaps my problem is that I am extracting text directly out of a word document and maybe the font is special and causing a problem??. From what I can see an apostrophe is normally ' (straight up and down), but in this string it is slanted forward, so maybe it is really a different special character?? I can definately see the apostrophe in the string, but after the Replace, it is still present as I am watching it in the debugger. Then, I tried seeing if I could find the position of the apostrophe with the IndexOf function, and it returned a -1. So my conclusion at this point, is it doesn't think it is an apostrophe character. I even tried using the Convert.ToChar(39) in the Replace and IndexOf functions to make sure it was looking for an apostrophe, and it didn't pick it up. Everything else seems to work. For example, I have a ~ sign in the string, and it finds this fine. FYI ... I am using ADO in the C# program to insert into an Oracle database.

 

[Rhys666]

What I would probably do the is put a breakpoint in and use the immediate window to find what character value is being identified at the index you're visibly seeing the apostrophe.

Other than that, using a stored procedure with the string value as an imput parameter should still solve general apostrophe issues.

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[LLDLLD]

To follow up for others reading this posting, I found that the apostrophe character that was pulled from a Word Doc (Times New Roman) ended up being ASCII character number 8217, which obviously is not the normal apostrophe - ASCII character number 39. Once knowing this, I could find/replace it without any issue. For example, strToReplace = strToReplace.Replace(Convert.ToChar(8217), Convert.ToChar(39));
Then, strTempObjectiveValues = strTempObjectiveValues.Replace("'", "''");
On the other hand, I might be able to insert it without issue since it isn't a standard apostrophe, but I haven't tried that yet. I also will look into using stored procedures, which was suggested.
Thanks to those who submitted the replies.

 

[Nemhain]

Ah, yes. Word processors and the like will often put nice-looking quotes and double-quotes around text. SQL Server will not regard that character as a straight apostrophe, so if that's your back-end, don't worry about it.

The C# code you posted at the beginning will work fine for dealing with the real ones.

 

[mbde]

Just one follow up on this you should be able to do this in one line of code instead of 2

string strText = String.Format("foo{0}bar{0}",Convert.ToChar(8217),Convert.ToChar(8217));

// Turn the char(8217) into a string, then replace with ''
strText = strText.Replace(Convert.ToChar(8217).ToString(), "''");