Currency Amount (Zero-Decimal Currency)
finance.currency.amount_nodecimalCurrency amounts for zero-decimal currencies (JPY, KRW, VND, CLP, ISK, HUF, and ~15 others). No fractional part. Comma thousands grouping with symbol prefix or suffix. broad_type is INTEGER.
Worked samples
DuckDB 1.5.5 · finetype 0.6.64
| Value | As VARCHAR | numeric_value | symbol |
|---|---|---|---|
| ¥1,234 | 1234 | 1234 | ¥ |
| ₩10,000 | 10000 | 10000 | ₩ |
| ¥100,000 | 100000 | 100000 | ¥ |
| ₩1,234,567 | 1234567 | 1234567 | ₩ |
Shape
^[¥₩\p{Sc}]?-?[0-9]{1,3}(,[0-9]{3})*[¥₩\p{Sc}]?$| Value | Length | Shape |
|---|---|---|
| ¥1,234 | 6 | not checked |
| ₩10,000 | 7 | not checked |
| ¥100,000 | 8 | not checked |
| ₩1,234,567 | 10 | not checked |
pattern uses syntax this checker does not support
Background
Origin
ISO 4217 is a standard published by the International Organization for Standardization that defines alpha codes and numeric codes for currencies and gives information about the relationships between currencies and their minor units.[1] The first edition of ISO 4217 was published in 1978.[1] SIX is the official Maintenance Agency for the ISO 4217 currency codes, providing that service on behalf of ISO.[2] The 2008 edition of ISO 4217 explains that a 0 in its Minor unit column means there is no minor unit for that currency, while 1, 2 and 3 signify ratios of 10:1, 100:1 and 1000:1.[1]
Japan's New Currency Act of 1871 divided the yen decimally into 100 sen or 1,000 rin.[3] The sen and the rin were taken out of circulation at the end of 1953.[3] The South Korean won is technically divided into 100 jeon, but the jeon is no longer used for everyday transactions and appears only in foreign exchange rates.[4]
Use
Java's Currency.getDefaultFractionDigits() returns the ISO 4217 minor unit, and its documentation gives 0 as the value for the Japanese Yen.[5] Unicode CLDR records for each currency the number of decimal digits normally formatted, with a default of 2.[6] The LDML specification says CLDR's digits value is based on the ISO 4217 minor unit but may deviate from ISO 4217 where there is compelling evidence for different customary practice.[6]
Stripe says ISK transitioned to a zero-decimal currency but, for backward compatibility, must be sent as a two-decimal value whose decimal part is always 00.[7]
The ¥ sign represents both the Japanese yen and the Chinese yuan when writing in the Latin script.[8] The yen and yuan sign is usually placed before the value, and is written JP¥50 or CN¥50 when disambiguation is needed.[8] Many early systems reassigned code point 5C, the backslash in US-ASCII, to the yen sign, because 7-bit US-ASCII had no code point for ¥.[8] Treat a bare ¥ as ambiguous: look for a currency code, a country column or decimals elsewhere in the column before assuming the amounts are yen. (Meridian's view) A value with a decimal point, such as ¥1,234.50, suggests the column is not yen-only, or that the source formats every currency with two decimals. (Meridian's view) Strip the symbol and the grouping commas and cast what is left to an integer, keeping the symbol or a currency code in its own column so the amount does not lose its currency. (Meridian's view)
Sources
- [1]ISO 4217secondary source
- [2]Financial Data Standardsorganisation
- [3]Japanese yensecondary source
- [4]South Korean wonsecondary source
- [5]Currency (Java SE 21 & JDK 21)documentation
- [6]Unicode Locale Data Markup Language (LDML) Part 3: Numbersstandard
- [7]Supported currenciesdocumentation
- [8]Yen and yuan signsecondary source
Detection notes
New in format coverage expansion. Zero-decimal currencies per ISO 4217: JPY, KRW, VND, CLP, ISK, HUF, etc. No fractional component.
Use it
$ finetype infer -i "¥1,234" --mode column
→ finance.currency.amount_nodecimal
SELECT ft_infer('¥1,234');
-- → 'finance.currency.amount_nodecimal'CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[¥₩\p{Sc}]', '', 'g'), ',', '', 'g') AS INTEGER)Schema
https://meridian.online/schemas/finance.currency.amount_nodecimal · Frictionless type string
finetype taxonomy finance.currency.amount_nodecimal -o json-schema