SQLite3 - Calculated SELECT with padding and concatenation

Question

I have the following SQLite table (a stub of the real table which has a few other columns)

CREATE TABLE IF NOT EXISTS fingers(id INTEGER,intLL INTEGER,fracLat INTEGER,fracLng INTEGER,PRIMARY KEY(id)) WITHOUT ROWID;

A typical entry in this table would be along the lines of

INSERT INTO fingers(id,intLL,fracLat,fracLng) VALUES(1,12899,42513,4025);

From time-to-time I need to query this table to pull out rows for matching intLL values in such a way that a calculated value meets a variable condition. For example

SELECT * FROM fingers WHERE intLL = 12899 AND ('8508' = (CAST((ROUND(CAST(fracLat AS REAL)/500)) 
AS INTEGER) || CAST((ROUND(CAST(fraCLng AS REAL)/500)) AS INTEGER)));

Explanation

• Transform the fractLat and fracLng columns by dividing them by 10,250 or 500. The CAST AS REAL is required to prevent the default integer division that would be performed by SQLite
• Round the decimal result to the closest integer. After rounding you will by default get a value with a trailing .0. The CAST AS INTEGER ensures that this is removed
• Concatenate the two parts. The concatenation is going wrong. In the present case the concatenated result would be 858 which is not what I want
• Compare against an incoming value: 8508 in this case.

My questions

1. How can I pad the two parts with 0s when required prior to concatenation so as to ensure that they have the same number of digits
2. Is there a simpler way of achieving this?

Answer 1

One way to pad 0s is to concatenate 00 at the start of the number and with SUBSTR() return the last 2 chars.
Also, you can divide by 500.0 to avoid integer division:

SELECT * FROM fingers 
WHERE intLL = 12899 
AND '8508' = SUBSTR('00' || CAST(fracLat / 500.0 AS INTEGER), -2) || 
             SUBSTR('00' || CAST(fraCLng / 500.0 AS INTEGER), -2)

Another way to do it is with the function printf() which formats a number:

SELECT * FROM fingers 
WHERE intLL = 12899 
AND '8508' = printf('%02d', fracLat / 500.0) || 
             printf('%02d', fraCLng / 500.0)

See the demo.