Noise characters in sql server

Question

Example:

INSERT INTO table.Diagnosis([DESCRIPTION]) VALUES('person's')

How can insert it?

what does this have to do with C#? You used the "C#" tag. Does this question relate specifically to passing in a value from C# code? That piece of info is critical to getting a correct answer here. Meaning, there are currently 3 answers and if this question is specific to sending data from C#, then @Steve 's answer is the only correct one. If not dealing with C#, then the other two (which are basically the same answer) are correct. — Solomon Rutzky, Sep 15 '14 at 17:39
You are giving an _"example"_, but don't say what for. You refer to inserting _"it"_, without saying what _"it"_ is. Your title mentions _"noise characters"_. Do you refer to the apostrophe? Is your question how a T-SQL string literal can contain apostrophes? — stakx - no longer contributing, Sep 15 '14 at 18:20

score 6 · Answer 1 · edited Sep 15 '14 at 17:37

6

'Person's' should actually be 'Person''s'. SQL Server thinks the string ends with that second single quote, so you have to "escape" it with an additional quote.

So your code as-is thinks it's trying to insert "Person" and anything after this is gibberish to SQL Server.

You'll need to modify your statement to the following:

INSERT INTO table.Diagnosis([DESCRIPTION]) VALUES('person''s')

edited Sep 15 '14 at 17:37

Chris Mantle

6,595
3
34
48

answered Sep 15 '14 at 17:30

Michael

599
6
11

score 2 · Answer 2 · edited May 23 '17 at 10:26

Because you have tagged this as C# I think that you want to set this value through code.
If this is the case then you probably have a string concatenation that ignores the presence of the single quote.

Something like this

string descValue = "person's";
string cmdText = "INSERT INTO table.Diagnosis([DESCRIPTION]) VALUES(" + descValue +")";

To avoid this problem you should use a parameterized query like this

string descValue = "person's";
string cmdText = "INSERT INTO table.Diagnosis([DESCRIPTION]) VALUES(@desc)";
using(SqlConnection cnn = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
   cnn.Open();
   cmd.AddWithValue("@desc", descValue);
   cmd.ExecuteNonQuery();

}

Using a parameterized query shifts the job to use your values to the database engine that now could correctly prepare your values and not blindly run the sql as is. This is true also for other types of values like decimals and dates. Last but not least, a parameterized query is the only way to avoid an Sql Injection attack

While I believe a parameterized query is overkill for inserting a constant string, you're making a good point. However, `descValue` should be set to `"person's"`, not `"'person's'"`: I think we can assume that the OP got the outermost apostrophes right (string literal delimiters), I suppose s/he doesn't want to insert them as part of the string value. — stakx - no longer contributing, Sep 15 '14 at 18:22
@stakx right about the descValue (a copy/paste error). For the query, well if it is a costant perhaps you are right, but I think that this is just an example. — Steve, Sep 15 '14 at 19:07

score 1 · Answer 3 · answered Sep 15 '14 at 17:32

1

Escape single quote with an additional single

INSERT INTO table.Diagnosis([DESCRIPTION]) VALUES('person''s')

answered Sep 15 '14 at 17:32

Tushar Gupta

15,504
1
29
47

Noise characters in sql server

3 Answers3