37 Votes

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

Info by Stefan Trost | Last update on 2022-12-30 | Created on 2013-05-13

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.

11 Vote

Or you can simple use for literals the expression since sqlite 3.8.4

printf('%0nd', <column>) where n = number of padding zeros
2020-09-30 at 05:27

ReplyPositive Negative

About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile


Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.