Inserting a value in a text field containing a single quote

[RiverGuy]

I'm using VB and an "Insert Into" statement. My only problem is that some of my field values have a single quote in them because they are possessive. I keep getting errors because my value will be as such:

Insert Into Table1([Field1]) Values('Ford's').

Anyone have an idea on how I can get around this?

 

[karluk]

The standard way of doing this is with two consecutive single quotes. Most databases, including SQL Server I believe, interpret this as a single quote and don't terminate the string. You insert statement would be

Insert Into Table1([Field1]) Values('Ford''s').

 

[RiverGuy]

That's a good suggestion. Thanks. What I ended up doing is a character comparison on the string and removing any single quotes before I used them to insert. I can get by without the quote there: i.e., "Fords". But I will try your suggestion next time.

Thanks agin.

 

[dogramone]

I have a little UDF that I use for this. I capture customer names and as such was having the same problem with single quotes. Herr it is.

CREATE FUNCTION dbo.ufc_STR_ReplaceSingleQuote (@InputString varchar(1000))
-- Replaces any single quotes with two quotes eg O'Brien become O''Briem
RETURNS varchar(1000)
AS
BEGIN
RETURN replace(@inputstring,'''','''''') -- Note: Parameter 2 of the replace statement is 4 quotes, Parameter 3 is 6 quotes.
END

 

[RiverGuy]

That's a good idea too.