MeridianMERIDIAN

Currency Amount (Multi-Character Symbol)

finance.currency.amount_multisym

Currency amounts with multi-character symbols: R$ (Brazilian Real), HK$ (Hong Kong Dollar), kr (Nordic Krone), Kč (Czech Koruna), zł (Polish Zloty). Separator conventions follow local standard.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

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

Shape

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

Told apart

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

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 symbol, normalizes separators.

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-]+|\s', '', '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