How do I use Char(39) in a sql statement where cla

Question

I am using SQL Server 2008 R2. I am trying to create a recordset based off a sql statement. In the WHERE clause is 'INTERNATIONAL PEOPLE' + Char(39) + 'S'. So the single quote makes it PEOPLE'S. This is failing. From research it seems this should not fail. Any help would be great. Thanks

Answer 1

Can you post your query? A escaping a single quote -''- should work. Test with the code below.

IF OBJECT_ID('TempDB..#SingleQuoteTest') IS NOT NULL
BEGIN
    DROP TABLE #SingleQuoteTest
END

CREATE TABLE #SingleQuoteTest
(
Value   VARCHAR(MAX)
)

INSERT INTO #SingleQuoteTest
(
Value
)
SELECT 'International People'
UNION SELECT 'International People''s'
UNION SELECT 'John O''connor'


SELECT  Value 
FROM    #SingleQuoteTest 
WHERE   Value = 'International People''s'
--WHERE Value = 'John O''connor'

Answer 2

The apostrophe s is essential element in writing reports. Here is an example. It gives the possessive property of a person in this case.

 SELECT TitleAndLastName = (Title + ' ' + LastName +CHAR(39) +'s' +  ' '  + 'address is:'), FullAddress = (AddressLine1 + City + cast(StateProvinceID as nvarchar) + PostalCode)
    FROM [Person].[Person] as p
        Inner Join [Person].[Address] as a
                   on a.AddressID = p.BusinessEntityID
            Where AddressLine1 is not null
             and Title is not null;

The result of the above query will have the ('s) of each person's last name.

TitleAndLastName FullAddress

Ms. Erickson's address is: 1226 Shoe St.Bothell 79 98011

Mr. Goldberg's address is: 1399 Firestone DriveBothell 79 98011

Good luck!

Answer 3

You can escape single quote by single quote :)

WHERE field = 'INTERNATIONAL PEOPLE''S'

Answer 4

From your sample in a comment, I'm guessing it's because your table is named 1099 and the error is not really from your WHERE clause. T-SQL expects table names to start with an alpha character.

Try putting brackets around that:

SELECT * FROM [1099] WHERE Name ='INTERNATIONAL PEOPLE' + CHAR(39) + 'S'