如何将字符串比较为整数 [英] How to compare a string as an integer

问题描述

在 SQL Server 2012 中，我有一组定义为 VARCHAR 的列.值是指标的阈值，可以是数字或字符串值.例如，温度阈值是数字 0 和 100 ，而门的阈值是 open 和 closed .

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.

我想测试一个值 [ResponseValue] ，以查看它是否在两个定义的阈值之间，即 [Value_Threshold_min] 和 [Value_Threshold_max] . [ResponseValue] 可以是 NUMERIC 或 VARCHAR ，但始终存储为 VARCHAR . Threshold_max/min 存储为 VARCHAR .

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

这不能正确测试数字值.例如，据说12在100到200之间，因为它被当作字符串来求值.

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.

我试图检查该值是否为数字，如果是，则将其强制转换为int，但是只要该值是字母数字，它都会失败.

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?

推荐答案

自2012年以来，您可以使用

Since you are on 2012, you can use TRY_CONVERT.

如果强制转换成功，则将强制类型转换为指定的数据类型；否则，返回null.

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

这篇关于如何将字符串比较为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！