David Winslow David Winslow - 2 months ago 8
SQL Question

How to compare a string as an integer

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" "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?

Answer
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