Meridianmeridian

Currency Amount (Trailing Negative)

finance.currency.amount_neg_trailing

Currency amount with trailing minus sign or CR/DR suffix for negative values. Standard in SAP, COBOL/mainframe exports, and bank statement formats. The negative indicator follows the amount.

Domain
finance
Category
currency
Casts to
VARCHAR
Scope
Universal

Try it

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

DuckDB

Detect
SELECT finetype('$1,234.56-');
-- → 'finance.currency.amount_neg_trailing'
Cast expression
CAST(CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN '-' || REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\s*(CR|DR)$', ''), '[\$€£¥₹]', ''), ',', '') ELSE REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\s*(CR|DR)$', ''), '[\$€£¥₹]', ''), ',', '') END 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_neg_trailing';

Struct Expansion

Expression
is_negative: CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN true ELSE false END

JSON Schema

finetype schema finance.currency.amount_neg_trailing
{
  "$id": "https://meridian.online/schemas/finance.currency.amount_neg_trailing",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "description": "Currency amount with trailing minus sign or CR/DR suffix for negative values. Standard in SAP, COBOL/mainframe exports, and bank statement formats. The negative indicator follows the amount.",
  "examples": [
    "$1,234.56-",
    "1,234.56 CR",
    "$999.99-",
    "10,000.00 DR"
  ],
  "pattern": "^[\\$€£¥₹]?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?\\s?(-|CR|DR)$",
  "title": "Currency Amount (Trailing Negative)",
  "type": "string",
  "x-finetype-broad-type": "VARCHAR",
  "x-finetype-transform": "CAST(CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN '-' || REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\\s*(CR|DR)$', ''), '[\\$€£¥₹]', ''), ',', '') ELSE REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\\s*(CR|DR)$', ''), '[\\$€£¥₹]', ''), ',', '') END AS DECIMAL(18,2))"
}

Examples

$1,234.56-1,234.56 CR$999.99-10,000.00 DR

Aliases

sap_amountcobol_amount