如何在 nvarchar 上使用 between 子句? [英] How to use between clause on a nvarchar?

问题描述

我有一张附在图片中的表格示例表值我希望这个条件会返回一行

I have a table which is attached in image here sample table values I want that this conditions will return one row

   where Vendor_Value_Table.Feature_ID in (17,19) and value_text like 'Dhol Wala$Shahnai Wala' and value_text between 0 and 100`

因为这代表单个 vendor_id但问题是 Value_Text 列的数据类型是 nvarchar(max)如何做到这一点，请提出建议.

because this represent to single vendor_id but the problem is datatype of Value_Text Column is nvarchar(max) how to do that kindly suggest.

推荐答案

假设您没有任何负数，您必须将 nvarchar 强制转换为 Int 以用于您的 between 子句，并为不可强制转换的情况设置默认值，如下所示:

Assuming you wont have ANY NEGATIVE NUMBER You must cast nvarchar to Int for your between clause and set a default for the case its not cast-able like below:

 SELECT *
  From Vendor_Value_Table
WHERE (Vendor_Value_Table.Feature_ID in (17,19)) 
AND(
value_text like 'Dhol Wala$Shahnai Wala' 
OR 
 (SELECT CASE WHEN ISNUMERIC(value_text) = 1 THEN CAST(value_text AS INT) ELSE -1 END) between 0 and 100
 )

我们选择 -1 作为默认值，因为如果它不能强制转换，则 between 子句需要始终为 false.

We chose -1 as default becuase if its not cast able to number the between clause needs to be false always.

(它只会响应小于 INT 范围的数字)

(IT WILL ONLY RESPONSE FOR THE NUMBERS LESS THAN INT RANGE)

这篇关于如何在 nvarchar 上使用 between 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！