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

Question

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?

Answer 1

I recommend avoiding overly-complex string manipulations by leveraging the power of the CASE statement. Try something like this:

CASE
  when d.target_grade is null then 'NA'
  when d.target_grade = 'N/A' then 'NA'
  when charindex('/', d.target_grade) = 0 then d.target_grade
  else substring(d.target_grade, charindex('/', d.target_grade) + 1, XX)  --  Replace XX with the max posssible length of d.target_grade
END