sqliteconcatenationcalculated-columnszero-padding

SQLite3 - Calculated SELECT with padding and concatenation


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

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?

Solution

  • 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.