set quoted identifier off in SQL

• ppa-186269

  Old Hand

  Points: 378

  More actions

  September 12, 2005 at 3:04 am

  #89764

  I'm trying to insert single code (') into the MS SQL database. But it always show the problem and what i did is i first run the

  "set quoted identifier off" command then,

  run the SQL statement.

  But it still has the problem.

  How can i fix this problem? If you have any idea and suggestions , please let me know?

  Thanks in advance.

• RoelofK68

  SSC-Addicted

  Points: 456

  More actions

  September 12, 2005 at 4:51 am

  #589030

  Please post your "INSERT" statement...

  <hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

  Roelof
  <a href='http://' class='authorlink' target='_blank'></a>

  --There are only 10 types of people in the world. Those who understand binary, and those who don't.

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  September 12, 2005 at 7:34 am

  #589052

  Use a single tick to escape it if you want to insert the quote, example:

  create table testcase (col1 varchar(100))

  go

  insert into testcase values ('One test '''' of inserting quotes ''''')

  go

  select * from testcase

  go

  col1                                                                                                

  -------------------------------

  One test '' of inserting quotes ''

  And look up BOL for "Using Identifiers" and "SET QUOTED_IDNETIFIER" to understand the difference between delimiting identifiers and literal strings.

   

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  September 13, 2005 at 11:15 am

  #589458

  quoted_identifier has nothing to do with it. It's basic rules about using single and double quotes with literal strings (string constants). These rules exist in all programming languages. Granted 'esoteric' in some ways but a fact of life. You can execute the modified example code below with quoted_identifier on or off:

   

  create table testcase (col1 varchar(100))

  go

  insert into testcase values ('One test '''' of inserting quotes ''''') --> all single quotes (overdone)

  insert into testcase values ('Two test '' of inserting quotes ''') --> all single quotes (correctly)

  insert into testcase values ('Three test " of inserting quotes "') --> double inside single quotes

  go

  select * from testcase

  go

   

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."