How to emulate LPAD/RPAD with SQLite

user_521799 0
Viewed 16309

I'm curious about how to emulate RPAD and LPAD functions for SQLite, formally, in the most general way. The goal is to be able to do

LPAD(column, character, repeat)
RPAD(column, character, repeat)

For non-constant table columns column, character, repeat. If character and repeat were known constants, then this would be a good, viable solution:

But what if the above should be executed like this:

SELECT LPAD(t.column, t.character, t.repeat) FROM t
SELECT LPAD(t.column, some_function(), some_other_function()) FROM t
SELECT LPAD(t.column, :some_bind_variable, :some_other_bind_variable) FROM t

How could this LPAD function be generally emulated? I'm lost with the possibilities:

A related question:

emulation padding sqlite sql
10 Answers

You could also PRINTF for the cases of 0 and space left padding:

sqlite> SELECT PRINTF('%02d',5);
05
sqlite> SELECT PRINTF('%2d',5);
 5
sqlite> SELECT PRINTF('%04d%02d',25,5);
002505
sqlite>

Starting from SQLite 3.38.0 (February 2022, introduced in this commit) printf becomes an alias for the FORMAT function for greater compatibility with other DBMSs. The function is documented at: https://www.sqlite.org/lang_corefunc.html#format FORMAT is not however standardized. and e.g. PostgreSQL 14 FORMAT does not recognize %d, only %s.

user_119680400

A simpler version of @user610650's solution, using hex() instead of quote(), and works with string padding in addition to char padding:

X = padToLength
Y = padString
Z = expression

select
    Z ||
    substr(
        replace(
            hex(zeroblob(X)),
            '00',
            Y
        ),
        1,
        X - length(Z)
    );
user_856420

Her's a simple solution to pad 0-9 with a leading zero using CASE.

sqlite> select id,week,year from bulletin where id = 67;
67|2|2014

select id,CASE WHEN length(week) = 2 THEN week 
               ELSE '0'||week 
          END AS week,year from bulletin where id = 67;
67|02|2014
user_1943780

I absolutely have no experience with SQLite, actually my time of interacting with SQLite3 db less then three days only. So I am not sure my findings could help anything to your requirement.

I am playing with some fun project of having all possible 11 digit phone number (3 digit operator prefix + 8 digit subscriber number). My target was to create some kind of database with minimum possible storage resource but must have to cover every possible number on database. So I created one table for 8 digit subscriber and another table contain 3 digit company prefix. Final number will come up on view joining two table data. Let me focus on LOAD Problem. As subscriber table column is INT, it is 0 to 99999999 individual record. Simple join fail for subscriber number having less then 10000000 ; any subscribers subscription id number valued under 10000000 shows up XXXprefix+11 where expecting XXX000000+11.

After failing with LPAD/RPAD on SQLite, I found "SUBSTR"!

Have a look on query bellow : 

CREATE TABLE subs_num (
subscriber_num integer PRIMARY KEY
);

INSERT INTO subs_num values ('1');
INSERT INTO subs_num values ('10');
INSERT INTO subs_num values ('100');
INSERT INTO subs_num values ('1000');

SELECT subscriber_num from subs_num;

SELECT SUBSTR('00000000' || subscriber_num, -8, 8) AS SUBSCRIB_ID FROM subs_num;

Now I think you can use SUBSTR for your LPAD/RPAD needs.

Cheers!

user_30890560

printf works for spaces, too:

SELECT 'text lpad 20 "'||printf("%020s", 'text')||'"'  paddedtxt UNION
SELECT 'text rpad 20 "'||printf("%-020s", 'text')||'"' paddedtxt UNION
SELECT 'num  lpad 20 "'||printf("%020d", 42)||'"'      paddedtxt

results to

num  lpad 20 "00000000000000000042"
text lpad 20 "                text"
text rpad 20 "text                "
user_79106160

Goal: Use available sqlite functions to mimic ltrim

Approach: (1) Use concat function "||" to add zeroes (or whatever the desired pad character is) to the left of the string, adding the number of zeroes equal to the number of characters you want left over, then adding a zero to the right. (2) Use substring to keep the desired number of characters.

Example: To pad page_number which was an integer with zeroes on the left to end up with five characters zero-filled to the left:

select

substring(('00000' || cast(page_number as text) || '0'), -1, -5)

from pages where classification = xxx'

Comment: substring seemed to ignore the last character when starting from -1, that's why I added a zero to the right - so it can be ignored. There's probably a more elegant way to do that.

user_195573120
Related