Single Quote in Query 2

[JohnMalv]

If I have a text field which I need to put some text and ascii data via a query, however that data contains one or more single quotes, is there a way to get that data in there without changing those quotes to a uniform "alternate" character? I tried \' already, and it still fails.

 

[nigelrivett]

insert tbl select 'O''Hare'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NoCoolHandle]

If it wasn't obvious, what Nigel did was "escape" one single quote with another...

I.E. to get a litteral single quote, send two.

 

[ESquared]

If your data is less than 256 characters (I believe) you may find the quotename function useful. Although, I see now in BOL that it returns Unicode. And also... weird... BOL says 258. I wonder if it's a typo.

 

[JohnMalv]

Interesting, though, in this instance, I'm quite certain the field is longer than 256 characters. In fact there are instances where it can be 10k .

Good advice though (though Unicode gives me nightmares.)