11

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.

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? Jan 17 '14 at 10:47
  • 3
    Just try lpad for sec. I think there was a bug (fixed now) and the functions where wrong.
    – Nathan W
    Jan 17 '14 at 10:50
  • 1
    Yes lpad works and converts 2 into 20. Jan 17 '14 at 10:51
  • 2
    Nope not broken.
    – Nathan W
    Jan 17 '14 at 21:18
  • 2
    What about just manual rpad('1', 2, '0')
    – Nathan W
    Jan 17 '14 at 22:33
12

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
    Oct 29 '19 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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