MeridianMERIDIAN

Currency Amount (Swiss Apostrophe)

Swiss/Liechtenstein format using apostrophe for thousands separator and period for decimal. Supports both straight apostrophe (U+0027) and right single quotation mark (U+2019).

Currency Amount (Swiss Apostrophe)

finance.currency.amount_apostrophe

Swiss/Liechtenstein format using apostrophe for thousands separator and period for decimal. Supports both straight apostrophe (U+0027) and right single quotation mark (U+2019).

Domain
finance
Category
currency
Casts to
VARCHAR
Scope
Universal

Try it

CLI
$ finetype infer -i "CHF 1'234.56"
→ finance.currency.amount_apostrophe

DuckDB

Detect
SELECT finetype('CHF 1'234.56');
-- → 'finance.currency.amount_apostrophe'
Cast expression
CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, 'CHF\s?', ''), '[''\x{2019}]', '', 'g'), '\s+', '') AS DECIMAL(18,2))
Safe cast pipeline
-- Normalise and cast in one step
SELECT TRY_CAST(finetype_cast(my_column) AS VARCHAR) AS clean_value
FROM my_table
WHERE finetype(my_column) = 'finance.currency.amount_apostrophe';

Struct Expansion

Expression
numeric_value: CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, 'CHF\s?', ''), '[''\x{2019}]', '', 'g'), '\s+', '') AS DECIMAL(18,2))
symbol: CASE WHEN {col} LIKE 'CHF%' THEN 'CHF' ELSE NULL END

JSON Schema

finetype schema finance.currency.amount_apostrophe
{
  "$id": "https://meridian.online/schemas/finance.currency.amount_apostrophe",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "description": "Swiss/Liechtenstein format using apostrophe for thousands separator and period for decimal. Supports both straight apostrophe (U+0027) and right single quotation mark (U+2019).",
  "examples": [
    "CHF 1'234.56",
    "1'234.56 CHF",
    "CHF 10'000.00",
    "-CHF 999.99"
  ],
  "pattern": "^(CHF\\s?)?-?[0-9]{1,3}(['\\x{2019}][0-9]{3})*(\\.[0-9]{1,2})?(\\s?CHF)?$",
  "title": "Currency Amount (Swiss Apostrophe)",
  "type": "string",
  "x-finetype-broad-type": "VARCHAR",
  "x-finetype-transform": "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, 'CHF\\s?', ''), '[''\\x{2019}]', '', 'g'), '\\s+', '') AS DECIMAL(18,2))"
}

Examples

CHF 1'234.561'234.56 CHFCHF 10'000.00-CHF 999.99

Aliases

amount_chswiss_amount

Type Registry