Search for numeric values from a varchar field in SQL server

[renu_kj]

Hi

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
Between ......

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.

[mail2saion]

You can try with below sample:

CONVERT(INT,SUBSTRING(Slab2A_Time_From,1,3))

First collect first 3 character then convert those into int. For example If your range length is 3 then substring 3rd parameter will be 3. Or you can move 1 by 1 like if firstcharacter is a nmuber then collect until nonnumeric character appeared.

[renu_kj]

Hi Thanks for the response..But i got a better solution to check whether the varchar can be converted to int.

CASE
WHEN ISNUMERIC(@FromContractExtRef2) = 0 THEN 0
WHEN @FromContractExtRef2 LIKE '%[^-+ 0-9]%' THEN 0
WHEN CAST(@FromContractExtRef2 AS NUMERIC(38, 0))
NOT BETWEEN -2147483648. AND 2147483647. THEN 0
ELSE 1
END