Post reply

select statement recognising apostrophe as a quotation mark

  • 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

  • August 20, 2014 at 2:26 am

    #1739493

    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/

  • August 20, 2014 at 2:28 am

    #1739494

    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

  • August 20, 2014 at 2:42 am

    #1739501

    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

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

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