Beast Mode - FORMAT Function

mdhsdi · August 2016

Currently Beast Mode does not support the MySQL FORMAT function:

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format

Since we can have summary numbers which are concatenated with strings, format would allow the number to be pre-processed with comma thousands separators.

Please enable this feature.

totesmgoets · August 2016

Let's make this happen! It'd be great for concat functions. One of those things the boss asks you about all the time!

product_John · August 2016

Thank you for submitting this idea @mdhsdi. Assigning to our product manager @ckwright for review.

ckwright · August 2016

@mdhsdi Thanks for this request, I will have my dev team look into this request. This definitely would provide more flexibility for formatting summary #'s.

totesmgoets · February 2017

@ckwrightAny update on this feature request?

Thanks,

Totes

ckwright · March 2017

@totesmgoets Thanks for reaching out, there is currently no update on this feature request. I'm working with our data function team to see what options we have for this request. Thanks.

totesmgoets · November 2017

@ckwright Has any progress been made on this yet? We still think it would be useful. Right now we have a pretty elaborate beast mode to get around it. You need to replace 'x' with your function. It's a mega pain, and there's no way it's not processing more than if you just used the FORMAT function.

CASE
      WHEN LENGTH(concat(round('x',0),'')) < 4 then concat(round('x',0),'')
      WHEN LENGTH(concat(round('x',0),'')) = 4 then CONCAT(SUBSTRING(concat(round('x',0),''),1,1), ',', SUBSTRING(concat(round('x',0),''),2,3))
      WHEN LENGTH(concat(round('x',0),'')) = 5 then CONCAT(SUBSTRING(concat(round('x',0),''),1,2), ',', SUBSTRING(concat(round('x',0),''),3,3))
      WHEN LENGTH(concat(round('x',0),'')) = 6 then CONCAT(SUBSTRING(concat(round('x',0),''),1,3), ',', SUBSTRING(concat(round('x',0),''),4,3))
      WHEN LENGTH(concat(round('x',0),'')) = 7 then CONCAT(SUBSTRING(concat(round('x',0),''),1,1), ',', SUBSTRING(concat(round('x',0),''),2,3), ',', SUBSTRING(concat(round('x',0),''),5,3))
      WHEN LENGTH(concat(round('x',0),'')) = 8 then CONCAT(SUBSTRING(concat(round('x',0),''),1,2), ',', SUBSTRING(concat(round('x',0),''),3,3), ',', SUBSTRING(concat(round('x',0),''),6,3))
      WHEN LENGTH(concat(round('x',0),'')) = 9 then CONCAT(SUBSTRING(concat(round('x',0),''),1,3), ',', SUBSTRING(concat(round('x',0),''),4,3), ',', SUBSTRING(concat(round('x',0),''),7,3))
      WHEN LENGTH(concat(round('x',0),'')) = 10 then CONCAT(SUBSTRING(concat(round('x',0),''),1,1), ',', SUBSTRING(concat(round('x',0),''),2,3), ',', SUBSTRING(concat(round('x',0),''),5,3), ',', SUBSTRING(concat(round('x',0),''),8,3))
      WHEN LENGTH(concat(round('x',0),'')) = 11 then CONCAT(SUBSTRING(concat(round('x',0),''),1,2), ',', SUBSTRING(concat(round('x',0),''),3,3), ',', SUBSTRING(concat(round('x',0),''),6,3), ',', SUBSTRING(concat(round('x',0),''),9,3))
      WHEN LENGTH(concat(round('x',0),'')) = 12 then CONCAT(SUBSTRING(concat(round('x',0),''),1,3), ',', SUBSTRING(concat(round('x',0),''),4,3), ',', SUBSTRING(concat(round('x',0),''),7,3), ',', SUBSTRING(concat(round('x',0),''),10,3))
END

Beast Mode - FORMAT Function

· Last Updated January 2021

Comments

Categories