12

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.

2 Answers 2

16

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'

1
  • Same here on QGIS 3.6.
    – kogexo
    Commented Oct 29, 2019 at 15:49
12

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'

8
  • 1
    I tried: rpad("Value", 2, '0') but nothing changes. Does that work with Integer field or do I need to convert to string? Commented Jan 17, 2014 at 10:47
  • 4
    Just try lpad for sec. I think there was a bug (fixed now) and the functions where wrong.
    – Nathan W
    Commented Jan 17, 2014 at 10:50
  • 1
    Yes lpad works and converts 2 into 20. Commented Jan 17, 2014 at 10:51
  • 2
    Nope not broken.
    – Nathan W
    Commented Jan 17, 2014 at 21:18
  • 2
    What about just manual rpad('1', 2, '0')
    – Nathan W
    Commented Jan 17, 2014 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.