user1870546 user1870546 - 1 month ago 8
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

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)

Comments