INSERT column value with imbedded apostraphe

Question

Post reply

INSERT column value with imbedded apostraphe

Express12

SSCoach

Points: 18514
More actions
July 29, 2005 at 8:41 am

#89319

Using the following sample, how can I INSERT a column value that contains an apostraphe w/in the CountryName?
Countries CountryCode INT, CountryName nvarchar(256)
insert into Countries (CountryCode, CountryName)
values (5, 'Cote D'Ivoire')
(Notice the imbedded apostraphe in the value: D'Ivoire)
thx-
BT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

insert into Countries (CountryCode, CountryName)

values (5, 'Cote D''Ivoire')

Should do it.

Prasad Bhogadi
www.inforaise.com

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

SET NOCOUNT ON

DECLARE @Countries TABLE

(

CountryCode INT,

CountryName VARCHAR(100)

)

INSERT INTO @Countries VALUES (1, 'Country Name 1')

INSERT INTO @Countries VALUES (5, 'Cote D''Ivoire')

SELECT * FROM @Countries

Regards,
gova

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

replace the apostrophe with 2 single quotes to indicate that it is a literal value...

insert into Countries (CountryCode, CountryName)

values (5, 'Cote D''Ivoire')

**ASCII stupid question, get a stupid ANSI !!!**

Casey Christensen Old Hand Points: 364 More actions · Answer 4

Actually, to add to this...

Every time you have another level of nested single quotes, you have to double the number of them you use. I ran across this when I had to write a query similar to yours, but the entire text of the query was a string in my code. So the result was something like:

'INSERT INTO @Countries VALUES (5, ''Cote D''''Ivoire'')'

Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

sara karasik SSCommitted Points: 1573 More actions · Answer 5

you might want to try this

insert..... 'Cote D' + char(39) + 'Ivoire'

I find that easier than keeping track of how many single quotes I need to type.

Sara