In SQL Server 2012,我有一组列定义为VARCHAR
。这些值是指标的阈值,可以是数字或字符串值。例如,温度的阈值是数字0
and 100
,而门的阈值是open
and closed
.
我想测试一个值[ResponseValue]
查看它是否在两个定义的阈值之间,[Value_Threshold_min]
and [Value_Threshold_max]
. The [ResponseValue]
可以是NUMERIC
or VARCHAR
但始终存储为VARCHAR
. The Threshold_max/min
存储为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 之间,因为它被视为字符串。
我尝试检查该值是否是数字,如果是数字,则将其转换为 int,但只要该值是字母数字,就会失败。
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
比较时如何将数字视为数字,将字符串视为字符串?
由于您是 2012 年,因此您可以使用TRY_CONVERT https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-2017.
如果转换成功,则返回转换为指定数据类型的值;
否则,返回 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)