How to emulate LPAD/RPAD with SQLite

问题:

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

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

回答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

回答3:

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.

回答4:

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

回答5:

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

回答6:

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
    )
  

回答7:

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!