Inserting fields with apostrophes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting fields with apostrophes

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-11-08 : 06:11:56

Hi there,

Could someone tell me how I can insert a string variable into a Varchar field when it has apostrophes in it?

Something like this:

String = "King's Lynn"

INSERT INTO dbo.Table (Field1) VALUES ('"& String &"')

...only this will produce a syntax error because of the apostrope in the string variable...any ideas?

Thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 06:19:16

Use QUOTENAME function with ' as quote character.

String = "King's Lynn"
INSERT INTO dbo.Table (Field1) VALUES (' + QUOTENAME(String, '''') + ')'

Or in client side

String = "King's Lynn"
INSERT INTO dbo.Table (Field1) VALUES ('" & REPLACE(String, "'", "''") & "')"

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-11-08 : 06:23:45

Perfect! Thanks as always :)

Gyto
Posting Yak Master

144 Posts

Posted - 2006-11-08 : 07:03:49

Is there a limit to how many times you can use REPLACE in a single SQL statement? ...because I used it for one field and it worked fine....then I added it to a second field of the same type and it came up as page cannot be displayed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 07:25:20

No, I don't think there is a limit.
Post your new problem here...

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-11-08 : 07:29:50

This works fine:

"INSERT INTO dbo.tEngineeringReport (PropertyNo, CostCentre, PropertyName, Address1, Address2, Address3, PostCode, ResStatus, AreaCode, ServType, DateInspected, DateNextInspection, FirmName, DescriptionOfWork)

VALUES ('"&PropertyNo&"', '"&CostCentre&"', '"&PropertyName&"', '"&Address1&"', '"&Address2&"', '"& REPLACE(Address3, "'", "''")&"', '"&PostCode&"', '"&ResStatus&"', '"&AreaCode&"', '"&ServType&"', '"&DateInspected&"', '"&DateNextInspection&"', '"&FirmName&"', '"&DescriptionOfWork&"')"

However this just produces a 'page cannot be displayed' message:

"INSERT INTO dbo.tEngineeringReport (PropertyNo, CostCentre, PropertyName, Address1, Address2, Address3, PostCode, ResStatus, AreaCode, ServType, DateInspected, DateNextInspection, FirmName, DescriptionOfWork)

VALUES ('"&PropertyNo&"', '"&CostCentre&"', '"&PropertyName&"', '"& REPLACE(Address1, "'", "''")&"', '"& REPLACE(Address2, "'", "''")&"', '"& REPLACE(Address3, "'", "''")&"', '"&PostCode&"', '"&ResStatus&"', '"&AreaCode&"', '"&ServType&"', '"&DateInspected&"', '"&DateNextInspection&"', '"&FirmName&"', '"&DescriptionOfWork&"')"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources