Bug report #4426

Add a number_format() function to the QgsExpression

Added by Mathieu Pellerin - nIRV almost 8 years ago. Updated about 7 years ago.

Status:Closed
Priority:Normal
Assignee:Nathan Woodrow
Category:Map Canvas
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed
Crashes QGIS or corrupts data:No Copied to github as #:14358

Description

It'd be very useful for have a number_format() function to allow formatting of numbers with grouped thousands. For example, number_format(12846) would result in the string '12,846'.

This function could come in very handy when used with the geometry values offered by the expression based labeling engine. For example, you could have nicely formatted area or length numbers by simply doing number_format($area) / number_format($length).

rounding_in_labels.PNG (3.26 KB) Alister Hood, 2011-10-24 10:41 PM

History

#1 Updated by Alister Hood almost 8 years ago

This would be an important feature. In the meantime you might be able to achieve what you need using something like this, although it isn't a particularly sustainable (~=fast and easy) solution:
regexp_replace( toint($area) ,'(\\\\d+)(\\\\d{3})','\\\\1,\\\\2')

EDIT - there seems to be some bug which stops that exact expression from working (it works OK in the expression based labels window's preview, but no labels are drawn on the canvas), although it works as part of a larger expression.

#2 Updated by Alister Hood almost 8 years ago

Also note that there is a "formatted numbers" section in the main labelling dialogue under "label settings", and:
1) It is a bit out of place there, as all the other options relate to label format rather than label content. Maybe it should be removed, superseded by the expression based labelling.
2) It has a "show plus sign" option, which currently has no equivalent in the expression based label dialogue (although I guess you could achieve it with regexp_replace).
3) Rather than reimplementing the "decimal places" option along with the thousands separator as part of number_format(), it might be best to implement a separate round() function, which would be more generally useful in the field calculator. It is already possible to round if you need to though - e.g. I rounded to 2 d.p. in the attached screenshot.

#3 Updated by Nathan Woodrow almost 8 years ago

  • Subject changed from Add a number_format() function to the expression based labeling engine to Add a number_format() function to the QgsExpression

Just changed the header as it's more related to the expression parser itself rather than the labeling per sa

#4 Updated by Alister Hood almost 8 years ago

Alister Hood wrote:

EDIT - there seems to be some bug which stops that exact expression from working (it works OK in the expression based labels window's preview, but no labels are drawn on the canvas), although it works as part of a larger expression.

Should be fixed now (everything seemed to work when I tried Nathan's patch on #4495)

#5 Updated by Mathieu Pellerin - nIRV almost 8 years ago

Alister, the expression above only adds one thousands separator, it's not recursive.

I.E. the number 12345 properly transforms into 12,345 but the number 12345678 ends up 12345,678.

#6 Updated by Alister Hood almost 8 years ago

Yes, that's why I said "might" and "something like this".

#7 Updated by Giovanni Manghi almost 8 years ago

  • Target version set to Version 1.7.4

#8 Updated by Paolo Cavallini over 7 years ago

  • Target version changed from Version 1.7.4 to Version 1.8.0
  • Crashes QGIS or corrupts data set to No
  • Affected QGIS version set to master

#9 Updated by Martin Dobias over 7 years ago

There are several things to consider:
  • locale (defines decimal character, group separator character and group separation rules)
  • number of decimal places
  • leading zeros
  • scientific notation

It seems there is no common way for doing that across existing SQL engines:
MySQL: format(number, decimal_places [, locale])
PostgreSQL, Oracle: to_char(number, format)
Ms SQL: str(number, output_length, decimal_places)

#10 Updated by Mathieu Pellerin - nIRV about 7 years ago

Nathan, loving all of the new functions you've recently added in the expression builder. The CASE / CASE ELSE addition is pure magic.

That said, I was wondering whether this bug is on your expression builder improvements agenda. Working with large numbers representing concession areas, the decimal formatting is a must for people to quickly read whether concession is in the thousands or tens of thousands of hectares.

Wish I could add the feature myself : /

#11 Updated by Paolo Cavallini about 7 years ago

  • Target version changed from Version 1.8.0 to Version 2.0.0

#12 Updated by Mathieu Pellerin - nIRV about 7 years ago

The function was added by NathanW in this commit https://issues.qgis.org/projects/quantum-gis/repository/revisions/fbfa025485369b12d1dd33f60bfa7d6802ac369d

Leaving NathanW with the honor of marking this bug closed -> fixed :)

That said, I have one little comment on the way this was implemented. I think the function should allow for a custom separator character to be defined. Relying on system locale setting for the separator can be problematic and is limiting in nature.

NathanW, thanks! : )

#13 Updated by Paolo Cavallini about 7 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

Also available in: Atom PDF