Home Forums SQL Server 2008 T-SQL (SS2K8) substring comparison for last 2 characters RE: substring comparison for last 2 characters

  • July 1, 2013 at 5:08 pm

    #1628859

    timscronin (7/1/2013)

    wont happen, the records with the 2 characters at the end are state abbreviations added on

    Then it's easy... Build a "State" table (you should have one anyway) and the your WHERE clause would look like the following...

    WHERE RIGHT(PatID,2) IN (SELECT StateAbbv FROM dbo.State)

    Be advised that no matter what you do, performance is going to stink because of the PatID column being wrapped in a function. What I'd recommend doing if you need to do this a lot is to make the RIGHT(Pat,2) a PERSISTED calculated column in the table and index it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)