MeridianMERIDIAN

Currency Amount (Multi-Character Symbol)

finance.currency.amount_multisym

Currency amounts with multi-character symbols: R$ (Brazilian Real), HK$ (Hong Kong Dollar), kr and kr. (Nordic Krone), Kč (Czech Koruna), zł (Polish Zloty). The symbol comes before the amount, or after it for Czech, Polish and Danish, as CLDR writes them (1 234,56 Kč, 1 234,56 zł, 1.234,56 kr.), with a space, a no-break space (U+00A0) or a narrow no-break space (U+202F) between the digit groups and before the symbol. Separator conventions follow local standard.

Try a value

Currency Amount (Multi-Character Symbol)'s rule accepts it.

Reads it as 1234.56

Worked samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs VARCHARsymbol
R$ 1.234,561234.56R$
HK$ 1,234.561234.56HK$
kr. 1.234,561234.56kr.
1 234,56 Kč1234.56Kč
1 234,56 zł1234.56zł

Shape

Pattern
^(?:(?:R\$|HK\$|NT\$|S\$|A\$|C\$|NZ\$|kr\.?|Kč|zł|Ft|lei|Rp|Rs\.?)[\s ]?-?[0-9]+(?:[.,\s ][0-9]+)*|-?[0-9]+(?:[.,\s ][0-9]+)*[\s ]?(?:kr\.?|Kč|zł))$
ValueLengthShape
R$ 1.234,5611matches
HK$ 1,234.5612matches
kr. 1.234,5612matches
1 234,56 Kč11matches
1 234,56 zł11matches
r$ 1.234,5611rejected: lower-cased

Told apart

Going the other way, this type's own rule accepts 1 of 6 samples of Currency Amount (Indian Lakh/Crore).

A trailing symbol is taken for kr, kr., Kč and zł only; the other symbols are prefixes.

Background

Origin

FineType defines this type itself, for currency amounts written with a symbol of more than one character, naming R$ (Brazilian Real), HK$ (Hong Kong Dollar), kr (Nordic Krone), Kč (Czech Koruna) and zł (Polish Zloty).[1] FineType's definition says the separator conventions in these amounts follow the local standard.[1]

The Brazilian real's sign is R$ and its ISO 4217 code is BRL.[2] The New Taiwan dollar's article explains that the $ sign usually precedes the amount, but NT$ is used to distinguish the currency from other currencies named dollar.[3] Wikipedia's article on currency symbols notes that the location of the symbol varies by language.[4]

The CLDR project says CLDR is incorporated into all modern operating systems and browsers.[5] In CLDR's English currency data, the Czech koruna's symbol is CZK, with Kč given as the narrow symbol.[6] In CLDR's English currency data, the Polish zloty's symbol is PLN, with zł given as the narrow symbol.[6]

Use

CLDR's Portuguese currency pattern is ¤ #,##0.00, which puts the currency symbol before the number.[7] CLDR's Portuguese number symbols use a comma as the decimal separator and a full stop as the grouping separator.[7] CLDR's Czech number symbols use a comma as the decimal separator and a no-break space as the grouping separator.[8]

LDML, the specification for CLDR's data, says the thousands separator and decimal separator in patterns are always ASCII ',' and '.', and are substituted with the correct local values from other CLDR fields.[9] LDML says the ¤ character in a pattern is replaced by the localised currency symbol for the currency being formatted.[9] LDML's currencySpacing element controls whether extra characters, such as a no-break space, are inserted between the currency symbol and the number.[9]

CLDR gives kr as the Swedish krona's symbol in Swedish.[10] CLDR gives kr as the Norwegian krone's symbol in Norwegian Bokmål.[11] A bare kr does not say which Nordic currency an amount is in, so keep a currency code column beside the parsed number, or take the currency from the file's documentation. (Meridian's view) Decide the decimal separator per currency or per file rather than per value: the same string can stand for two very different numbers depending on which convention the file follows. (Meridian's view) Treat the symbol as a label to extract and check, not noise to discard, because the number alone loses the currency it was written in. (Meridian's view)

Sources

  1. [1]finetype/labels/definitions_finance.yaml at main · meridian-online/finetype · GitHubFineType
  2. [2]Brazilian real - Wikipediasecondary source
  3. [3]New Taiwan dollar - Wikipediasecondary source
  4. [4]Currency symbol - Wikipediasecondary source
  5. [5]Unicode CLDR Projectorganisation
  6. [6]cldr-json/cldr-numbers-full/main/en/currencies.jsonstandard
  7. [7]cldr-json/cldr-numbers-full/main/pt/numbers.jsonstandard
  8. [8]cldr-json/cldr-numbers-full/main/cs/numbers.jsonstandard
  9. [9]Unicode Locale Data Markup Language (LDML) Part 3: Numbersstandard
  10. [10]cldr-json/cldr-numbers-full/main/sv/currencies.jsonstandard
  11. [11]cldr-json/cldr-numbers-full/main/nb/currencies.jsonstandard

Detection notes

New in format coverage expansion. Multi-character symbols generalize locale-specific parsing. Transform strips a leading or trailing symbol and every space, including U+00A0 and U+202F, and normalizes separators. Source: CLDR (cs, pl, da currency patterns). U+202F is spelled out in the pattern so every regex engine accepts it. A trailing symbol is taken for kr, kr., Kč and zł only; the other symbols are prefixes.

Use it

CLI · DuckDB
$ finetype infer -i "R$ 1.234,56" --mode column
→ finance.currency.amount_multisym

SELECT ft_infer('R$ 1.234,56');
-- → 'finance.currency.amount_multisym'
Cast expression
CAST(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[^0-9-]+|[^0-9]+$|[\s\x{A0}\x{202F}]', '', 'g'), '[.,]([0-9]{1,2})$', 'D\1'), '[.,]', '', 'g'), 'D', '.') AS DECIMAL(18,2))

Schema

https://meridian.online/schemas/finance.currency.amount_multisym · Frictionless type string

finetype taxonomy finance.currency.amount_multisym -o json-schema

Type Registry