How do I insert a single quote ' into a table? For example:
Insert mytable values (1,''')
I get an error message.

Any ideas? The server does not recognize double quote (why? is this
tsql, ansi?), otherwise I could have written it as:
Insert mytable values (1,"'")

Thanks.

Nevermind. Following worked:
SET QUOTED_IDENTIFIER OFF
insert mytable values (3,"'",getdate())

othell...@yahoo.com wrote:
> How do I insert a single quote ' into a table? For example:
> Insert mytable values (1,''')
> I get an error message.
>
> Any ideas? The server does not recognize double quote (why? is this
> tsql, ansi?), otherwise I could have written it as:
> Insert mytable values (1,"'")
>
> Thanks.

(othellomy@yahoo.com) writes:
> Nevermind. Following worked:
> SET QUOTED_IDENTIFIER OFF
> insert mytable values (3,"'",getdate())


Yes, that's one way to do it. But it will not always work, because the
setting QUOTED_IDENTIFIER must be ON in some situations.

The standard way to do it is do double the string delimiter:

insert mytable values (3, '''', getdate())

Standard SQL only recognizes ' as a string delimiter. " is used to delimiter
identifiers with "funny" characters in them, like space. (Although with SQL
Server you normally use [] in this case.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

To add on to Eland's response, the ANSI standard QUOTED IDENTIFIER ON allows
you to use SQL Server features line indexes on views and computed columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<othellomy@yahoo.com> wrote in message
news:1163056886.515598.327620@h48g2000cwc.googlegr oups.com...
> Nevermind. Following worked:
> SET QUOTED_IDENTIFIER OFF
> insert mytable values (3,"'",getdate())
>
> othell...@yahoo.com wrote:
>> How do I insert a single quote ' into a table? For example:
>> Insert mytable values (1,''')
>> I get an error message.
>>
>> Any ideas? The server does not recognize double quote (why? is this
>> tsql, ansi?), otherwise I could have written it as:
>> Insert mytable values (1,"'")
>>
>> Thanks.
>

Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes:
> To add on to Eland's response, the ANSI standard QUOTED IDENTIFIER ON
> allows you to use SQL Server features line indexes on views and computed
> columns.

And in SQL 2005, XQuery.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx