Currency Amount (US Format)

finance.currency.amount_us

Currency amount with US/international formatting: optional currency symbol prefix ($, £, ¥, etc.), optional negative sign or parentheses for negative, comma thousands separator, period decimal separator. Examples: $1,234.56, -$999.99, £10,000.00, $0.50, ¥1,000

Domain

finance

Try it

CLI

$ finetype infer -i "$1,234.56"
→ finance.currency.amount_us

DuckDB

Detect

SELECT finetype('$1,234.56');
-- → 'finance.currency.amount_us'

Cast expression

CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' || REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\-]+', ''), '[^0-9\.\-]', '', 'g'), ',', '', 'g'), '\s+', '', 'g') AS DECIMAL(18,2))

Safe cast pipeline

-- Normalise and cast in one step
SELECT TRY_CAST(finetype_cast(my_column) AS DECIMAL) AS clean_value
FROM my_table
WHERE finetype(my_column) = 'finance.currency.amount_us';

Struct Expansion

Expression

numeric_value: CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' || REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\-]+', ''), '[^0-9\.\-]', '', 'g'), ',', '', 'g'), '\s+', '', 'g') AS DECIMAL(18,2))
symbol: REGEXP_EXTRACT({col}, '^([^0-9\-\(]+)')

JSON Schema

finetype schema finance.currency.amount_us

{
  "$id": "https://meridian.online/schemas/finance.currency.amount_us",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "description": "Currency amount with US/international formatting: optional currency symbol prefix ($, £, ¥, etc.), optional negative sign or parentheses for negative, comma thousands separator, period decimal separator. Examples: $1,234.56, -$999.99, £10,000.00, $0.50, ¥1,000",
  "examples": [
    "$1,234.56",
    "-$999.99",
    "£10,000.00",
    "$0.50",
    "¥1,000",
    "$42.00",
    "$1,000,000.00",
    "£250.75",
    "($1,234.56)",
    "($999.99)"
  ],
  "pattern": "^[\\$£¥₹₩₿\\p{Sc}]?\\s?-?\\s?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?$|^-?[\\$£¥₹₩₿\\p{Sc}]\\s?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?$|^\\([\\$£¥₹₩₿\\p{Sc}]?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?\\)$",
  "title": "Currency Amount (US Format)",
  "type": "string",
  "x-finetype-broad-type": "DECIMAL",
  "x-finetype-transform": "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' || REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\\-]+', ''), '[^0-9\\.\\-]', '', 'g'), ',', '', 'g'), '\\s+', '', 'g') AS DECIMAL(18,2))"
}

Examples

$1,234.56-$999.99£10,000.00$0.50¥1,000$42.00$1,000,000.00£250.75($1,234.56)($999.99)

Aliases

currency_amountmoney_usprice