I have a column named 'ExtRefNo' which of varchar datatype.
The values can be numbers alone(eg; 101) & values comprising characters & numbers(eg:101A3)
My requirement is if the user search for ExtRefNo between 100 & 105,
it shud retrieve all ExtRefNos ranging from 100,101,102,103,104 & 105.
Need not include the ExtRefNo, if it is 101A3.
I used Isnumeric() in a Case statement
Something like this..don't know the correct syntax
Case When Isnumeric(@FromContractExtRef2) = 1 and Isnumeric(@ToContractExtRef2) = 1
Then Cast(Contract.ExternalRef2 as int)
Else @FromContractExtRef2 End
The Problem is that if the user search for ExtRefNo 101A3...it should only find the record that has got this ExtRefNo 101A3 , ie if the search values entered for @FromContractExtRef2 and @ToContractExtRef2 is numeric..then it should find all the ExtRefNo between that range..else should find the ExtRefNo which is like @FromContractExtRef2.
Hope this makes sense.