floating point - SQL Server throwing error in SQL for Odd / Even check

Question

I have a SQL Server database that is throwing an error on one of my tables when I add a check for an even or odd number value.

Here is an example script which would cause the error:

SELECT TOP 5 * 
FROM [TABLE_QUALIFIER].[TABLE_OWNER].[TABLE_NAME] WITH (nolock) 
WHERE street_number BETWEEN '1' AND '5000' 
  AND (street_number % 2) > 0 
  AND street_name LIKE '%Main St%' 
ORDER BY CAST(street_number AS float);

As you can see, I have this piece of the statement checking for odd results:

AND (street_number % 2) > 0

If I eliminate this bit of the call, I get a successful query result. However, in this case I need to include the check of odd street number results.

When I check the table info with this command:

exec sp_columns [TABLE_NAME];

I can see that the street_number column is saved as a varchar type. In the SQL statement, you can see that I am casting the street_number value as a float.

Any suggestions on how I can amend this SQL query to remove the error when checking for an odd street_number?

Additional info:

Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) Jan 23 2020 21:00:04 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

question from:https://stackoverflow.com/questions/65905672/sql-server-throwing-error-in-sql-for-odd-even-check

Answer 1

Here is a conceptual example for you. It includes DDL and sample data population.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, street_number VARCHAR(10), street_name VARCHAR(50));
INSERT INTO @tbl (street_number, street_name) VALUES
('1', 'Main St.'),
('10', 'Not so Main St.'),
('20', 'Wall St.'),
('10A', 'Wall St.');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT ID, street_name
        , TRY_CAST(street_number AS INT) AS street_no
    FROM @tbl
)
SELECT TOP(5) * 
FROM rs
WHERE street_no BETWEEN 1 AND 5000
  AND (street_no % 2) > 0 
  AND street_name LIKE '%Main St%' 
ORDER BY street_no;

Output

+----+-------------+-----------+
| ID | street_name | street_no |
+----+-------------+-----------+
|  1 | Main St.    |         1 |
+----+-------------+-----------+