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_tex

Solution 1:

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 
 (SELECTCASEWHEN ISNUMERIC(value_text) =1THENCAST(value_text ASINT) ELSE-1END) between0and100
 )

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)