Fix SQL query which is ugly and confusing

Question

I directly access the internal MS SQL Server software package. The key field (vehicle number) is defined as alpha, but we enter a numerical value in the field. There is only one exception, we put an “R” in front of the number when the car retires (this means that we sold it or the car was hacked). Assuming that users are doing this correctly, we should not run into a problem using this method. (Right or wrong is not a problem here)

Fast Forward I am trying to request a subset of these car numbers (800 - 899) for some special handling. Making the range from “800” to “899” we also get 80, 81, etc. If I specify the car number in INT, I should be able to get the correct range. Except that these "R" cars are kicking me in the ass now.

I tried where vehicleId not like 'R%' and cast(vehicleId as int) between 800 and 899, however, I got a casting error on one of these R cars.

Which works where vehicleId not between '800' and '899' and cast(vehicleId as int) between 800 and 899', but I believe there should be a better way and a less confusing way.

I also tried other options with HAVINGand a subquery all producing a casting error.

Answer 1

, :

where cast(replace(vehicleId,'R','') as int) between 800 and 899

Answer 2

_ %:

WHERE vehicleId LIKE 'R8__' OR vehicleId LIKE '8__'

:

WHERE vehicleId LIKE '%8__'

Answer 3

, ( SQL):

where vehicleId not like 'R%' and cast(vehicleId as int) between 800 and 899 

, .

:

WHERE
    CASE
        WHEN vehicleId NOT LIKE 'R%' THEN
            CAST(vehicleId as int)
        ELSE
            0
    END BETWEEN 800 AND 899 

Answer 4

:

WHERE vehicleId BETWEEN '800' and '899' AND LEN(vehicleId) = 3

, .

Answer 5

, , .

- , nonnumerics teh int

select * from 
(select * from mytable where vehicleId not like 'R%')a
where cast(vehicleId as int) between 800 and 900

, ,      ( * from mytable, isnumeric (vehicleId) = 0) a    (vehicleId as int) 800 900

There are some problems with isnumeric, but if they work, you can write the isint () function and use this instead.

Answer 6

where 1=
(
    case 
        when 
            vehicleId like 'R%' then 0 
        else 
            (case when cast(vehicleId as int) between 800 and 899 then 1 else 0 end) 
    end
)

Answer 7

I'm not sure if you want to include the "R%" records and the variable or not the VehicleIds value range, but that might help.

WHERE vehicleId LIKE '8[0-9][0-9]' 
OR vehicleId LIKE 'R8[0-9][0-9]'

If the range is a variable, you might need something like this.

WHERE CASE WHEN vehicleId LIKE 'R%' THEN CAST(SUBSTRING(vehicleId,2,99) AS INT)
    WHEN vehicleId LIKE '[0-9][0-9][0-9]' THEN CAST(vehicleId AS INT)
    END BETWEEN 800 AND 899

Answer 8

I do something like this when dealing with such things ...

DECLARE @Autos Table  (
ID varchar(100)
)

Insert into @Autos (ID) values ('R1')
Insert into @Autos (ID) values ('2')
Insert into @Autos (ID) values ('3')
Insert into @Autos (ID) values ('R4')
Insert into @Autos (ID) values ('5')
Insert into @Autos (ID) values ('R52')
Insert into @Autos (ID) values ('53')
Insert into @Autos (ID) values ('R8')

Select * from @Autos
Where cast(REPLACE(ID, 'R', '0') as integer) between 2 and 55
and ID not like 'R%'

There is an exit,

ID
---
2
3
5
53

- Chris