How to format labels with leading zeroes?

Question

I'm trying to display a number next to a symbol in QGIS 2.0.1 - The numbers are from an integer-field in a PostgreSQL database.

The goal is that single digit numbers get a leading 0 (zeros). Examples:

• 12 --> 12
• 10 --> 10
• 9 --> 09
• 0 --> 00

In the menu

 layer properties --> labelling --> formating

I switch on "number formatting" and then enter a custom expression. I tried these two codes with various combinations of ", ' and more brackets, but none of them work:

• CASE WHEN "Value" < 10 THEN rpad("Value", 2, 0) END
  

• CASE WHEN "Value" < 10 THEN "0" || "Value" END
  

For any other person having trouble with this: Turn of "number formatting". It seems to be the reason why leading zeros are removed from the rpad() function.

Enter the following in the regular "layer labeling" box:

  rpad("Field", 2, 0)

and everything will work fine. But if you also activate number formatting it will take away the leading zeros.

Answer 1

Working on QGIS 2.18.20 I see a different behavior of the one described by @NathanW:

lpad("id",2,'0')-> '02'

rpad("id",2,'0')-> '20'

Where I think r stands for right and l for left.

Quoting the man page of the function rpad:

Returns a string padded to supplied width using a fill character.

Syntax

rpad(string, width, fill)

Arguments

string string to pad

width length of new string

fill character to pad the remaining space with

Examples

rpad('Hello', 10, 'x') → 'Helloxxxxx'

Answer 2

There is the rpad() function for that:

input	function	output
1	rpad('1', 2, '0')	01
10	rpad('10', 2, '0')	10

For using this function for your field:

rpad("yourcolumn", 2, '0')

rpad() function

Returns a string with supplied width padded using the fill character.

Syntax rpad(string, width, fill)

Arguments

string - is string. The string.
width - is int. The length of the new string.
fill - is char. The character to padd the remaining space with.

Example

rpad('Hello', 10, 'x') → 'xxxxxHello'