Apostrophe problem

I'm stumped by what seems to be an apostrophe problem. Basically, I have a query that does this:

...

WHERE provider = @.Provider

...

When @.Provider has an apostrophe, no results are returned, even though an independent query of the database shows that there should be records returned. When I repace the apostrophy with double apostrophes, that doesn't improve things, either.

Just as a by-the-way, I would think that double apostrophe's are not needed in this situation, since I'm not building any strings with @.Provider. Regarless of that, though, it doesn't work either way. What am I missing?

Thanks!

As this example demonstrates, double apostrophies are required if the sought value contains a single apostrophy.

Code Snippet

DECLARE @.MyTable table
( Provider varchar(20) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'Smith' )
INSERT INTO @.MyTable VALUES ( 'O''Donald' )
INSERT INTO @.MyTable VALUES ( 'Bill''s Wife' )

DECLARE @.Provider varchar(20)
SET @.Provider = 'O''Donald'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

SET @.Provider = 'Bill''s Wife'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

|||Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question. Sorry.|||

Perhaps one of the moderators felt that it provided a clear and unambiguous response to the question as asked.

If a field contains an apostrophe, then the way to indicate that the criteria value also contains an apostrophe is to use double apostrophes. The examples I provided clearly demonstrate that is the case. Putting apostrophes into text boxes to terminate the string is one of the oldest forms of SQL Injection hacking.

If your @.Provider is set to a string containing an apostrophe, then every character past the apostrophe is ignored.

@.Provider = 'O'Connor becomes @.Provider = 'O'. Depending upon how the code is written, the error may be ignored.

If you feel that your question has not been answered, perhaps you should 'revise' the question and resubmit it.|||

Double apostrophe is needed... otherwise the server would never know you're referring to the special character.... and not the end of a string.

|||

...or perhaps people who read it should read it to the end and pay attention rather than jump to conclusions and effectively shut down the thread. After all, the original question states clearly that "When I repace the apostrophy with double apostrophes, that doesn't improve things, either." Clearly, I'm aware of the double-apostrophe solution! Anyway, no hard feelings. I've found a workaround at this point.

|||

When I repace the apostrophy with double apostrophes, that doesn't improve things, either."

Since you never gave any indication about what that statement meant, it could only be ignored.

Perhaps what is, is that the original question was not properly stated and/or did not have enough information to provide the solution you were seeking. I suggest that instead of quibbling with the responses, it would be more productive to provide more information about the problem -we can't read your mind. Revision and restatement is often more productive that accusations and retorts.

Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question.

Embedded single quotes must be 'escaped' (doubled) when used as part of a query string criteria. As far as I am aware, under 'normal' circumstances, there is no 'work-around' for embedded single quotes.

However, there is the non-standard use of QUOTED_IDENTIFIER. By setting it off, you would use double quotes for the string delimiters and have embedded single quotes. That 'could' cause other unexpected issues with the database and/or applications. From Books Online:

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

|||

Hi Jararaca

Are you certain that your @.Provider variable contains a classic apostrophe (i.e. CHAR(39)) rather than CHAR(96), which looks similar but isn't treated as being an apostrophe by SQL Server?

If this turns out to be the problem you could (preferably) modify the code that sets the value of @.Provider, or, if that is beyond your control, use:

WHERE Provider = REPLACE(@.Provider, CHAR(96), CHAR(39))

Failing this, and assuming that your query is embeded in a stored procedure, I would set up a SQL Profiler trace in order to capture the values of the parameters passed in to the stored procedure - this way you should be able to immediately spot any problems with the value of @.Provider.

Chris

|||This is it! Thank you Chris. I went back and checked the data and this is exactly what's going on. Much appreciated!|||Arnie, if you change the correct answer in this thread again, I'll report you to the forum adminstration.|||

I don't have time to go back and 'change the correct answer'.

I would rather spend my time helping others rather than quibbling over the 'worth' of a suggestion. I put them out, some work, some don't. That's not my call. As was indicated earlier, there are a quite a few moderators, and perhaps someone considered the suggestion a good response to the question as asked.

But public accusations, especially from someone that hides behind an anonymous nom de plume -now that's getting downright 'unfriendly' and not worthy of further discourse ...

p.s., I'm glad that your issue was resolved.

|||

Ooo. Below the belt, dude... in so many ways. I challenge you to a duel, sir! (face slap with white gloves!)

Maybe we should just lighten up. I appreciate your gladness over the resolution of my issue. And thank you in return for your attempt to help.

|||Color me lighter...