CODEWITHSUNDEEP
sqlsql-serversql-server-2005casting
AJ_
AJ_

Reputation: 3987

SQL Server 2005 - Converting failure

I'm selecting values from a table. Inside the table there is a column called value. I'm trying to select that value with a 'g' at the end of the value when the value is a number. However, when i try to do this i get the following error (see below).I think this is because i'm using ISNUMERIC, and since the values are either a string representation of a number or the string value 'None' (i know weird, but i have to have this way), i get a convert failure. Anyone know how to fix this issue in SQL Server 2005

Error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'None' to data type int.

Query

SELECT a.student_assignment_id,        
           a.student_id,        
           a.location_id,        
           a.assignment_type,
            (
            Case 
                WHEN a.assignment_type = 'HW' THEN
                    CASE a.value 
                    WHEN ISNUMERIC(a.value) THEN RTRIM(a.value)+'g'
                    ELSE a.value
                    END
                ELSE a.value
            END 
            )  as value
            ,        
           a.start_date,        
           a.end_date,        
           a.course,              
           a.created_by,        
           a.creation_date,        
           a.modified_by,        
           a.modified_date,

Upvotes: 0

Views: 29

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

You need to use CASE WHEN ISNUMERIC(a.value) = 1 instead of CASE a.value WHEN ISNUMERIC(a.value)

CASE WHEN a.assignment_type = 'HW' 
     THEN
         CASE WHEN ISNUMERIC(a.value) = 1 
              THEN RTRIM(a.value)+'g'
              ELSE a.value
              END
     ELSE a.value
     END

ISNUMERIC() returns a boolean 0 (not numeric) or 1 (numeric), so your current query is attempting to compare the string a.value to an int (0 or 1)

Upvotes: 1

Related Questions