Apostrphee in text

[cricketer1]

How can I handle the apostrophee in text. I know that to output: 'It's me', I can use PRINT('It''s me'). But how do I handle this in a user entered string input variable that needs to be input in a table. Should the entire string variable be searched for the apostrophee and where ever found insert another apostrophee before it....is there a beteer way?

Thanks

 

[tlbroadbent]

We haven't found a better way to overcome the "apostrophe problem" than replacing one ' with two ' via VB, VBA, or other language code. I'd be interested to know if someone has a beter way. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[cricketer1]

Thanks, and if I stumble on a solution to this apostrophee problem, I'll post it as a tip. But it made me feel better to know that I am not the only one doing it this way.

cricketer1

 

[ColinM]

Can't you use double quotes like this? (Or does this not work from a front-end?)

insert #temp values ("it's me&quot

 

[cricketer1]

This wasn't working on my end, but after some research I found that I had SET QUOTED_IDENTIFIER ON, which means that I cannot use double quotes to delimit literals (strings: "it's me&quot. However, I can use double quotes to delimit identifiers (table, column etc. names).

If I SET QUOTED_IDENTIFIER OFF then I can do
PRINT("It's me&quot but then I cannot use double quotes to delimit the object names that I declare in SQL Server which do not follow T-SQL rules for identifiers.

So for all who come across this problem, check your SET QUOTED_IDENTIFIER ON/OFF option when dealing with strings that have an apostrophee embeded in them

Thanks

 

[fluteplr]

Part of the problem is that what you have in the database is not an apostrophe [`], but a single quote [']. I have taken the road of replacing all the single quotes in the data with actual apostrophies and putting code in my text box and edit box classed that replaces a single quote with an apostrophe if anyone types one in.

The set quoted identifiers works to some extent, until you get to databases with spaces in their object names. In SQL2000 you can work around this by using square brackets as object delimiters, I can't remember if this works in 7.