jeudi 28 mai 2015

SQL Server 2005 - RIGHT() not working when adding to CHARINDEX()

I'm trying to use the RIGHT function to get the substring of a value if it consists of a '/', but it doesn't work when I add a number to the CHARINDEX value; only without.

Here is a sample of the code:

SELECT CASE
            WHEN 
                CHARINDEX('/',REPLACE(ISNULL(d.target_grade,'NA'), 'N/A', 'NA')) = 0
            THEN 
                REPLACE(ISNULL(d.target_grade,'NA'),'N/A','NA')
            ELSE 
                RIGHT(d.target_grade, CHARINDEX('/',REPLACE(ISNULL(d.target_grade,'NA'), 'N/A', 'NA'))+1)
        END as target_grade
FROM tbl --etc.

This returns for example

target_grade
-------------
C/D

It should return though this

target_grade
-------------
D

If I remove the +1, however, the RIGHT function works exactly as it should

target_grade
-------------
/D

What am I doing wrong here? Is my logic flawed?

Aucun commentaire:

Enregistrer un commentaire