How to emulate LPAD/RPAD with SQLite

Question

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:

• http://verysimple.com/2010/01/12/sqlite-lpad-rpad-function/

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:

• http://www.sqlite.org/lang_corefunc.html

A related question:

• How to emulate REPEAT() in SQLite

Motion

What are you using to connect to SQLite? The database engine has a "plugin" architecture where you can define additional functions (UDFs) and include them in your sql... – Agostino

@Stobor: Good point. I'm connecting with an inofficial JDBC driver. This is all to be implemented in jOOQ, a SQL abstraction layer in Java. So unfortunately, I cannot rely on possible UDFs, only on what's provided in SQLite core – Motion

Are you developing for jOOQ, or using the jOOQ api? Either way, I'm pretty sure you can use java UDFs... – Agostino

@Stobor: I'm the creator of jOOQ. Thus, I'd prefer not to create a dependency on this particular JDBC driver. But your solution is still quite nice for someone who might be using the jOOQ API. If those UDFs can be discovered using SQLite's pragmas, then jOOQ should support them natively! – Motion

Answer 1

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)
    );

Patrica

Answer 2

Copied from http://verysimple.com/2010/01/12/sqlite-lpad-rpad-function/

-- the statement below is almost the same as
-- select lpad(mycolumn,'0',10) from mytable

select substr('0000000000' || mycolumn, -10, 10) from mytable

-- the statement below is almost the same as
-- select rpad(mycolumn,'0',10) from mytable

select substr(mycolumn || '0000000000', 1, 10) from mytable

Maxim

This is an elegant solution except (I believe), in cases where the length of the value is more than 10...in which case, the extra left-side characters are truncated. For example, SELECT SUBSTR('00000' || 'ABCDEFG', -5, 5) results in 'CDEFG'. This may be very unwanted and silent behavior – Allgood

Answer 3

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.

Lurie

Only useful for zero-padding numbers. Not working for strings: SELECT printf('%010s', '8') → ' 8' – Amidst

Answer 4

Here's more nastiness for you:

X = padToLength
Y = padString
Z = expression

RPAD (for LPAD, Z is concatenated after instead):

select 
    Z || 
    substr(
        replace(
            replace(
                substr(
                    quote(zeroblob(((X - length(Z) - 1 + length(Y)) / length(Y) + 1) / 2)), 
                    3
                ), 
                "'", 
                ""
            ), 
            "0", 
            Y
        ),
        1,
        (X - length(Z))
    )

Examples:

sqlite> select "foo" || replace(replace(substr(quote(zeroblob((2 + 1) / 2)), 3, (2 - length("foo"))), "'", ""), "0", "W");
foo
sqlite> select "foo" || replace(replace(substr(quote(zeroblob((7 + 1) / 2)), 3, (7 - length("foo"))), "'", ""), "0", "W");
fooWWWW

Sqlite is meant to be quite lightweight, so I have to disagree somewhat with your comment about being "surprised" by the lack of functionality. However, I agree that there should be a simpler way to do padding, if only because the trim functions exist.

Convolve

Hah, well that looks quite crazy! I'll check later to see if this works :-) I know about being lightweight. But LPAD / RPAD can be coded in 10 lines of C code. On the other hand, I still fail to see a use-case for randomblob()... – Motion

This really works! quote() did the magic although it didn't make things more readable when having to remove the X'...' stuff again... :) I'll award the bounty if no one comes up with any shorter solution (which I doubt). Anyway, your solution will make it into jOOQ, such that no future SQLite user will ever have to think about simulating LPAD and RPAD again! – Motion

@LukasEder: taz cool; is jOOQ some equivalent for EF in .Net? – Convolve

Not really. EF would map to JPA in Java. You could compare jOOQ with LINQ-to-SQL, I guess – Motion

Hmm, a minor drawback of your solution is the fact that character really just has to be a single character. Some LPAD() and RPAD() functions support padding with strings, e.g. RPAD('abc', 7, 'xy') = 'abcxyxy'. I guess that would be the final kill for readability, though :-) – Motion

Indeed SQL doesn't make a difference between a character and strings; but for sure it can be done, it'll probably be just be nastier. – Convolve

@LukasEder: I adapated to support string padding. Let me know how it works for you (I did not update the examples however). – Convolve

Quick note: I see that MySql's function will actually trim if the string is longer than the padToLength. My answer doesn't do that and I don't think it should as I don't think padding functions should trim. It should be left to the programmer to apply both functions if need be. – Convolve

Interesting, yes. I don't think trimming should be applied, either. Padding should result in a minimal string length, not exact string length, no matter if the length is surpassed because of the padded character(s) or because of the original string – Motion

Answer 5

A JDBC/custom functions approach (may not be suitable in your exact case, but might be able to be adapted). Uses inspiration from SqliteJDBC Custom Functions and the rightPad and leftPad functions from Apache commons.lang.StringUtils:

import java.sql.*;
import org.sqlite.Function;

public class Test 
{
  public static void main(String[] args) 
  {
    Connection conn = getConnection();

    conn.createStatement().execute("SELECT LPAD(t.column, t.character, t.repeat) FROM t");
    conn.createStatement().execute("SELECT RPAD(t.column, t.character, t.repeat) FROM t");

    conn.close();
  }

  public static Connection getConnection() 
  {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:");

    /* Left Padding UDF  */
    Function.create(conn, "LPAD", new Function() 
      {
        protected void xFunc() throws SQLException 
        {
            String text = value_text(0);
            /* uses first character of supplied padding */
            char paddingCharacter = value_text(1).charAt(0);
            int repeat = value_int(2);

            int padLength = repeat - text.length();
            if(padLength <= 0)
            {
               result(text);
            }

            char[] padding = new char[padLength];
            Array.fill(padding, paddingCharacter);
            result(new String(padding).append(text));
        }
    });

    /* Right Padding UDF  */
    Function.create(conn, "RPAD", new Function() 
      {
        protected void xFunc() throws SQLException 
        {
            String text = value_text(0);
            /* uses first character of supplied padding */
            char paddingCharacter = value_text(1).charAt(0);
            int repeat = value_int(2);

            int padLength = repeat - text.length();
            if(padLength <= 0)
            {
               result(text);
            }

            char[] padding = new char[padLength];
            Array.fill(padding, paddingCharacter);
            result(text.append(new String(padding)));
        }
    });
  }
}

(Untested, off the cuff, doesn't handle nulls, etc, but should outline the idea...)

Agostino

Very nice, I didn't know that was possible! In my particular case, I'm not sure if I want to create UDF's that are dependent on this JDBC driver's capabilities. But in general, it's a nice solution – Motion

@LukasEder: yeah, it's slightly less practical in a multi-purpose library, but there are some possibilities. If it's okay as a build-dependency but not a runtime dependency, then you could have a database specific "initializer" class, which is loaded (via reflection) if the connection class string matches "org.sqlite" or something. But yeah, it's not the only way, and if you have better methods, you could always use them instead. – Agostino

Answer 6

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

Oddment

Yes that works for padding something to a fixed length. But even if this fixed length is, say, 10, this solution is going to blow up in verbosity... – Motion

Answer 7

Maybe like this:

• LPAD(@orig_text, @padding_char, @padding_length):

  SELECT
    SUBSTR(
      REPLACE(
        CAST(ZEROBLOB(@padding_length) AS TEXT),
        CAST(ZEROBLOB(1) AS TEXT),
        @padding_char
      ) + @orig_text,
      -@padding_length,
      @paadding_length
    )
  

• RPAD(@orig_text, @padding_char, @padding_length):

  SELECT
    SUBSTR(
      @orig_text + REPLACE(
        CAST(ZEROBLOB(@padding_length) AS TEXT),
        CAST(ZEROBLOB(1) AS TEXT),
        @padding_char
      ),
      1,
      @padding_length
    )
  

Cartouche

It's a nice idea, but it doesn't seem to work. Looks like casting a zeroblob(N) value to TEXT results in an "empty" string. In C strings are terminated by the 0x00 character, which is what's contained in the zeroblob :-( – Motion

Answer 8

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!

Counterproposal

This has already been suggested here – Motion

Answer 9

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                "

Decameter

Answer 10

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.

Judaize

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Mcphee