I have a situation in which I need to do one thing if the values of a field ("ServiceCode") fall within the range of 100-299, and do something else if the value is outside this range. The problem is that ServiceCode field has a datatype of char(6), and there are some non-numeric values in that field.
So the logic needs to be:
If ServiceCode is a number datatype (int or decimal) AND ServiceCode is between 100 and 299, then <Do Something>
Additionally, this statement needs to be within a larger Select statement.
<LOGIC FROM ABOVE>,
I do not understand what u mean by "then <Do Something>" and how u want to do it. However if u want a set of values if the logic passes then something like below can be used
declare @ServiceCode varchar(10)
case when isnumeric(@ServiceCode)=1 then
(case when (cast(@ServiceCode as decimal(8,3)) > 100) then 'number and greater than 100' else 'not of my interest' end)
else 'not of my interest' end
My guess may be correct if I say that by <Do Something> would mean error message if required value is not inputted. While inputting values in the ServiceCode field, first of all the values should be numeric and has no effect whether it is a char datatype.
You can proceed by the following SQL statement:
SELECT IF(ASCII(servicecode)=servicecode AND ASCII(servicecode)=>100 AND
ASCII(servicecode)<=299,servicecode,"Out of range") FROM table_name
I've tried my best to give you the solution. Try this one.
SET NOCOUNT ON
CREATE TABLE myTable99(Serv_CD varchar(6))
INSERT INTO myTable99(Serv_CD)
SELECT '11a' UNION ALL SELECT '150' UNION ALL SELECT '999'
SELECT CASE WHEN CONVERT(int, Serv_CD) BETWEEN 100 AND 299 THEN Serv_CD ELSE null END AS Serv_CD
WHERE ISNUMERIC(Serv_CD) = 1
SET NOCOUNT OFF
DROP TABLE myTable99