How can I format a decimal in xquery?

Question

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 ()
                )
};

Good question, +1. See my answer for a complete and short solution :) — Dimitre Novatchev, May 01 '11 at 18:02
It is not required to use any namespace prefix for the standard XPath functions. If you omit the `"fn:"` prefix, your code will be more readable. Also, at present your coding is inconsistent: why you prefix the `concat()` function, but not the `substring()` function? — Dimitre Novatchev, May 02 '11 at 01:35
@Dimitre: I don't usually prefix standard functions, but I copied this function from the website noted above. It's inconsistent because I didn't strip out the prefixes when I made the modifications. I'll have to go through and clean it up so it doesn't confuse anyone. Thanks again. Your help is very much appreciated! — Daniel Haley, May 02 '11 at 01:48

score 3 · Accepted Answer · answered May 01 '11 at 17:57

3

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

answered May 01 '11 at 17:57

Dimitre Novatchev

240,661
26
293
431

Thank you very much Dimitre. Based on this answer, I updated the function that I was using. +1 and answer accepted. – Daniel Haley May 02 '11 at 01:08

Dennis Münkle · Answer 2 · 2012-03-26T17:19:36.467

1

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).

edited Mar 26 '12 at 17:19

answered Apr 30 '11 at 20:11

Dennis Münkle

5,036
1
19
18

2

No, `format-number()` isn't supported. That was the first thing I tried. I tried changing from HE to PE & EE, but I still get an error: `F [Saxon-EE XQuery 9.3.0.4] System function format-number#2 is not available with this host language`. +1 for a good suggestion though – Daniel Haley Apr 30 '11 at 20:49
Tried `fn:format-number($myelement/*:betrag cast as xs:decimal,"#,##0.00")` in **XQuery 3.0** and with Saxon-HE 9.7 Parser and it works. – the hand of NOD Mar 05 '19 at 15:52

score 0 · Answer 3 · answered Mar 06 '19 at 07:03

With XQuery 3.0 and Saxon-HE 9.7 Parser you can do the following:

declare decimal-format local:de decimal-separator = "," grouping-separator = ".";
declare decimal-format local:en decimal-separator = "." grouping-separator = ",";
let $numbers := (1234.567, 789, 1234567.765)
for $i in $numbers
return (
   format-number($i,"#.###,##","local:de"),
   format-number($i,"#,###.##","local:en")
)

The output is:

<?xml version="1.0" encoding="UTF-8"?>1.234,57 1,234.57 789,0 789.0 1.234.567,76 
1,234,567.76

How can I format a decimal in xquery?

3 Answers3

Linked