I'm trying to format decimals in XQuery. The decimals are currency, so the format should be ,###.##
.
For example:
5573652.23
should be 5,573,652.23
and
352769
should be 352,769
(or 352,769.00
if it's easier/cleaner)
Right now I'm using this function from http://www.xqueryhacker.com/2009/09/format-number-in-xquery/, but I can't use decimals with it:
declare function local:format-int($i as xs:int) as xs:string
{
let $input :=
if ($i lt 0) then fn:substring(fn:string($i), 2)
else fn:string($i)
let $rev := fn:reverse(fn:string-to-codepoints(fn:string($input)))
let $comma := fn:string-to-codepoints(',')
let $chars :=
for $c at $i in $rev
return (
$c,
if ($i mod 3 eq 0 and fn:not($i eq count($rev)))
then $comma else ()
)
return fn:concat(
if ($i lt 0) then '-' else (),
fn:codepoints-to-string(fn:reverse($chars))
)
};
I'm using Saxon 9HE for my processor.
Any help would be greatly appreciated.
----- UPDATE -----
Based on Dimitre's answer, I modified the function to save the decimal portion and add it to the end of the return string.
New Function
declare function local:format-dec($i as xs:decimal) as xs:string
{
let $input := tokenize(string(abs($i)),'\.')[1]
let $dec := substring(tokenize(string($i),'\.')[2],1,2)
let $rev := reverse(string-to-codepoints(string($input)))
let $comma := string-to-codepoints(',')
let $chars :=
for $c at $i in $rev
return (
$c,
if ($i mod 3 eq 0 and not($i eq count($rev)))
then $comma else ()
)
return concat(if ($i lt 0) then '-' else (),
codepoints-to-string(reverse($chars)),
if ($dec != '') then concat('.',$dec) else ()
)
};
Use:
let $n := 5573652.23
return
concat(local:format-int(xs:int(floor($n))),
'.',
substring(string($n - floor($n)), 3)
)
This produces exactly the wanted, correct result:
5,573,652.23
This doesn't work for you?:
format-number(5573652.23,",###.##")
You can play with this here. I am pretty sure that saxon supports this function.
Edit: This function is not supported in saxon (see comments below).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With