single quote

[othellomy]

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]

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

[e-mail] 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.

[Erland Sommarskog]

(ot*******@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, es****@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

[Dan Guzman]

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

<ot*******@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

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

[e-mail] 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.

[Erland Sommarskog]

Dan Guzman (gu******@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, es****@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