select statement recognising apostrophe as a quotation mark

Question

Post reply

select statement recognising apostrophe as a quotation mark

Tania Mofflin

Ten Centuries

Points: 1021
More actions
August 20, 2014 at 2:08 am

#289181

Hello
I'm trying to do a query which includes
CASE WHEN LEFT(LASTNAME,2) = 'O'' THEN .......
I'm trying to Find O' in a Last Name field, as in O'Brien. But it is recognising the apostrophe as a closed quotation mark and returned the error message..
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'O' THEN 1 ELSE 2 END as [Test] from UPR00100 order by LASTNAME
'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'O' THEN 1 ELSE 2 END as [Test] from UPR00100 order by LASTNAME
The THEN part of the CASE will be a lot more complex I was just focusing on getting it to recognise first..
Thankyou

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You need to add one more apostrophe. See my example bellow:

declare @Name varchar(10)

set @Name = 'O''braien'

SELECT CASE WHEN LEFT(@Name,2) = 'O''' THEN 'yes' else 'no' end, @Name

set @Name = 'Obraien'

SELECT CASE WHEN LEFT(@Name,2) = 'O''' THEN 'yes' else 'no' end, @Name

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Escape the quote by doubling it

LEFT(LastName,2) = 'O''' -- That's two single quotes to escape the ', then a third one to close the string

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tania Mofflin Ten Centuries Points: 1021 More actions · Answer 3

Thanks I thought it would be simple but I've never come across that issue 🙂

GilaMonster (8/20/2014)
Escape the quote by doubling it
LEFT(LastName,2) = 'O''' -- That's two single quotes to escape the ', then a third one to close the string