user1870546 user1870546 - 10 months ago 57
SQL Question

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`


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.

Answer Source

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
 )

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

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