How to compare a string as an integer

Question

In SQL Server 2012, I have a set of columns that are defined as VARCHAR. The values are thresholds for a metric and can be either numbers or string values. For example, the thresholds for temperature are numbers 0 and 100, whereas, thresholds for door would be open and closed.

I want to test a value [ResponseValue] to see if it's in between two define threshold values, [Value_Threshold_min] and [Value_Threshold_max]. The [ResponseValue] can be either NUMERIC or VARCHAR but always stored as VARCHAR. The Threshold_max/min are stored as VARCHAR.

SELECT responsevalue 
        , (CASE WHEN ResponseValue BETWEEN steps.value_threshold_min AND steps.Value_Threshold_max else END THEN column_color 
FROM steps 
JOIN responses on responses.stepid = steps.id 
WHERE stepid = 4447

This does not properly test values that are numeric. For example, 12 is said to be in between 100 and 200 because it's being evaluated as a string.

I've tried to check if the value is numeric and if so, cast it to int but this fails whenever the value is alpha numeric.

CASE WHEN isnumeric([Responses].[ResponseValue]) = 1 
THEN CAST([Responses].[ResponseValue] as int) 
ELSE [Responses].[ResponseValue] END) 
BETWEEN steps.value_threshold_min AND steps.Value_Threshold_max THEN column _color 

How can I treat a number as a number and string as a string when comparing?

Solution

Since you are on 2012, you can use TRY_CONVERT.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

SELECT responsevalue,
    CASE 
        WHEN TRY_CONVERT(INT,ResponseValue) BETWEEN TRY_CONVERT(INT,steps.value_threshold_min) AND TRY_CONVERT(INT,steps.Value_Threshold_max) THEN 'Int Color'
        WHEN ResponseValue BETWEEN steps.value_threshold_min AND steps.Value_Threshold_max THEN 'VarcharColor'
        ELSE NULL
    END as column_color 
FROM steps 
JOIN responses on responses.stepid = steps.id 
WHERE stepid = 4447

Comments

[David Winslow]

I get the same error as I do with isnumeric. Conversion failed when converting the varchar value 'Yes' to data type int. [SQL State=S0001, DB Errorcode=245]. The values in responseValue can be numbers or strings. When they are numbers, I need to treat them as numbers for the "between" function to work. I can't treat everything like numbers because some of the values are strings.

[S3S]

No worries @DavidWinslow

[Gordon Linoff]

@DavidWinslow . . . Did you then use try_convert() as I recommended?

[S3S]

@GordonLinoff I think it's on the responsevalue column but could be wrong