SQLite: RPAD and LPAD in SQLite - Fill string with characters left or right

Problem: To pad the result from a query with an arbitrary character on the left or on the right, MySQL is providing the functions RPAD and LPAD (Right Padding and Left Padding). For example, this can look like this:

SELECT col;   SELECT LPAD(col, 3, '-');  SELECT RPAD(col, 3, '-');
1             --1                        1--
22            -22                        22-
333           333                        333

Depending on the length of the contents of "col", the result will be filled with the specified character on the left to the desired length using LPAD or accordingly on the right using RPAD. In our example, we demonstrate this with a hyphen as a padding character and a filling length of 3 characters.

Unfortunately, SQLIte is not providing such a function, so that we have to use a trick to get the same results.

This is how it works with SQLite

As a solution, we are using a mixture of string concatenation (this is the || operator in SQLite) and SUBSTR:

First, here is the equivalent to LPAD:

LPAD(col, 3, '-') FROM ...
SUBSTR('---' || col, -3, 3) FROM ...

And here is the equivalent to RPAD:

RPAD(col, 3, '-') FROM ...
SUBSTR(col || '---', 1, 3) FROM ...

Explanation: The trick is working in the following way: First, we read the contents of "col", then we add the string "---" to the result. This string is as long as the maximum length of characters that can be padded. So, the result of this query alone might be "---1" or "---22".

After that, we are using SUBSTR to shorten the resulted string according to our desired length. So, the result from "---1" would be "--" and the result from "---22" would be "-22". With this, we have our padded string.

We are performing this procedure either at the front or at the back, depending on whether we would like to imitate LPAD or RPAD. To make the example fit for your own needs, you have to replace the string "---" with the padding characters you would like to use (in a sufficient length) and you have to replace "-3" and "3" with your desired padding length.