As d3ck0r suggested.  adding a byte_length() function would enable padding
of spaces [but not general padding with arbitrary characters as lpad() and
rpad() afford].

WITH points(p) AS (VALUES ('abc'), ('äöü'), ('です'))
,format(f) AS (VALUES ('%*s'), ('%-*s'))
,pad AS (SELECT p, f, printf(f,byte_length(p)+(4-length(p)),p)pad FROM
points CROSS JOIN format)
SELECT p,f,pad,length(pad)len FROM pad;

'abc','%*s',' abc',4
'abc','%-*s','abc ',4
'äöü','%*s',' äöü',4
'äöü','%-*s','äöü ',4
'です','%*s','  です',4
'です','%-*s','です  ',4

A new byte_length() function is a great idea but for getting action on
publishing it and the requisite help page entry.
I recently asked to add 1 protection source line in the eval() function
against segmentation fault but got neither action nor reply.
Experience suggests you will have to add the 3 source lines to your local
copy of SQLite if you must to pad strings containing high code points:

static void byte_length(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));


On Mon, Feb 19, 2018 at 12:43 AM, Ralf Junker <> wrote:

> On 18.02.2018 00:36, Richard Hipp wrote:
> The current behavior of the printf() function in SQLite, goofy though
>> it may be, exactly mirrors the behavior of the printf() C function in
>> the standard library in this regard.
> SQLite3 is not C. SQLite3 text storage is always Unicode. Thus SQL text
> processing functions should work on Unicode. The current implementation
> of the SQLite3 SQL printf() can not reliably be used for string padding.
> And there is no simple alternative, AFAICS.
> PostgreSQL returns 4 in all cases:
> select
>    length(format ('%4s', 'abc')),
>    length(format ('%4s', 'äöü')),
>    length(format ('%-4s', 'abc')),
>    length(format ('%-4s', 'äöü'))
> MySQL has lpad() and rpad() to achieve the same and also returns 4 in
> all cases:
> select
>    length(lpad ('abc', 4, ' ')),
>    length(lpad ('äöü', 4, ' ')),
>    length(rpad ('abc', 4, ' ')),
>    length(rpad ('äöü', 4, ' '))
> I strongly believe that SQLite3 should follow suit.
> Ralf
> _______________________________________________
> sqlite-users mailing list
sqlite-users mailing list

Reply via email to