How to insert data with single qoute?

Hi guys, Anyone of you kindly reply on this.

Thanks guys,

Make the single quote as double-single-quotes:
select 'Chris''s'
will select, Chris's
Hope this helps,
-chris|||Thanks a lot Chris...........|||

Hi emkcah,

Chris suggestion is valid, but beware if the character data has two (or more) singe quotes as this will then fail (as in the string You're at Chris's). Also, the suggestion is erroneous as your souce data is in essence now corrupt and no longer portable.

In this scenario, you should:

SET QUOTED_IDENTIFIER OFF

GO

SELECT "You're at Chris's"

Cheers

Rob

|||Thanks Robert |||

You can use a function to check user input for a "single quote" and add the second "single quote" if you're adding records based on user input into a form. The following function changes a last name such as "O'Day" to "O''Day".

Function call:

lastname = fixapostrophe(Server.HtmlEncode(lastname))

Function:

Private Function fixapostrophe(ByVal newText As String) As String
Dim apostrophe As String = "'"
Dim pos As Integer
Dim start As Integer = 1
Do While InStr(start, newText, apostrophe) > 0
pos = InStr(start, newText, apostrophe)
newText = newText.Insert(pos, apostrophe)
start = pos + 2
Loop
Return newText
End Function

It seems to work OK, but you should check with a real programmer before using it.

|||There's a much easier solution, just use the replace function :
select (replace,"'","`",field1) from tablename
eg insert into [destination table] select replace("'","`",fieldname) from [sourcetable]
look it up on books on line..

|||

Hello,

Using Replace will corrupt your souce data. If the string "you're" is passed to the app, it should remain as "you're" and stored as such.

Cheers

Rob