Member Avatar for 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.

  • 2 Contributors
  • 2 Replies
  • 246 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by renu_kj
Member Avatar for 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.

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.