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`
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)