Quotation marks

Hi
Can someone tell me what i am doing wrong below:
--
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe'+"'"+'s')
select * from Tb1
Drop table Tb1
--
I would like the 2nd column to appear as Joe's in the resultset.
Thank you in advanceTry this:
INSERT INTO Tb1 VALUES('7', 'Joe''s';
HTH
Vern
"MittyKom" wrote:

> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Oops, forgot the closing parenthesis:
INSERT INTO Tb1 VALUES('7', 'Joe''s');
"Vern Rabe" wrote:
> Try this:
> INSERT INTO Tb1 VALUES('7', 'Joe''s';
> HTH
> Vern
> "MittyKom" wrote:
>|||escape single quote with a single quote
like
'joe''s'
(P.S: that not a double quote, its 2 single quotes :)|||Hi MittyKom
There is no need for any concatenation of strings. If you use two single
quotes inside outer single quotes, it is interpreted as one single quote in
the string.
So your use of concatenation is unnecessary but your use of the double
quotes (") is incorrect. Most interfaces have a setting called
QUOTED_IDENTIFIER set to on, which means that double quotes are used only to
delimit identifiers, and not user data. So the message you are receiving
refers to the fact that your single quote inside the double quotes is being
interpreted as an identifer, and it makes no sense.
So the cleanest solution is to just make it all one string to insert into
the second column, with the two adjacent single quotes getting interpreted
as one single quote in the string.
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe''s')
select * from Tb1
Drop table Tb1
The other solution is to SET QUOTED_IDENTIFIER OFF, and then your original
solution will work (but if you leave it on, other things might break)
HTH
Kalen Delaney, SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Thank you so much Vern and Omnibuzz.
"Omnibuzz" wrote:

> escape single quote with a single quote
> like
> 'joe''s'
> (P.S: that not a double quote, its 2 single quotes :)
>|||I use char(39) I think..
Insert Into Emp (LastName) Values ("O" + char(39) + "clock") -- O'clock
something like that.
I know there are quotes/inside other quotes methods, but those sometimes
come back to haunt me, since I deal with client's databases that I don't
have full control over.
..
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance