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
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.0
. The CAST AS INTEGER
ensures that this is removedMy questions
One way to pad 0
s 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.