Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wooldawg
Starting Member
12 Posts |
 Posted - 2008-03-24 : 16:42:57
|
| OK, so I know that following works if I want update/insert the value "joe's bar" into the table. set SET QUOTED_IDENTIFIER onupdate #temp set id=4 where name ='joe''s bar'The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :) |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-24 : 23:40:32
|
| It's kind of escape character. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-03-25 : 08:47:55
|
| I think you are confusing two seperate (but related) things.SET QUOTED_IDENTIFIER ON means that whenever Sql Server sees a double quote <"> it assumes that you are attempting to identify (or define) a sql object by name, ie: a table name, column Name, etc... So whenever sql server sees something like "joe's bar" it would assume it is the name of an object like:SET QUOTED_IDENTIFIER ON create table [Joe's Bar] ([i] int)--is the same ascreate table "Joe's Bar" ("i" int)So when QUOTED_IDENTIFIER is ON you can't use double quotes to define a VALUE because sql server will interperate the string as an object name. When QUOTED_IDENTIFIER is OFF you can use double quoted to define a value. ie: SET QUOTED_IDENTIFIER is OFFdeclare @myVarchar varchar(10)set @myVarchar = "Joe"set @myVarchar = 'Joe'That is quoted identifier and has nothing directly to do with ESCAPing imbedded special characters.So when the value contains a single quote (like apostrophe) and QUOTED_IDENTIFIER is OFF you can say,"Joe's Bar" successfully.But when QUOTED_IDENTIFIER is ON you would have to use single quotes to define the string. When the value itself contains a single quote you need to ESCAPE the character. An ESCAPE character is an agreement to treat the character immidiately following the escape character as a literal character rather than a special character. In this case, when the apostrophe is ESCAPEDed then it is not treated as the end of the string.Since sql server uses a single quote as the escape character, when you want to have a literal single quote in a string you need to preceed it with the escape character, ie: SET QUOTED_IDENTIFIER is ON --default settingdeclare @myVarchar varchar(10)set @myVarchar = 'Joe''s Bar'Be One with the OptimizerTG |
|
|
|
wooldawg
Starting Member
12 Posts |
Posted - 2008-03-25 : 09:13:59
|
| Thanks TG,That was very helpful! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
I don't understand quoted_identifier