Currency Amount (Trailing Negative)
finance.currency.amount_neg_trailingCurrency 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.
Operations on this type: Parse nested values
Try a value
Currency Amount (Trailing Negative)'s rule accepts it.
Reads it as -1234.56
Use it
finetype infer -i '$1,234.56-' --mode column
# → finance.currency.amount_neg_trailingSELECT ft_infer('$1,234.56-');
-- → 'finance.currency.amount_neg_trailing'CAST(CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN '-' || REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\s*(CR|DR)$', ''), '[\$€£¥₹]', ''), ',', '', 'g') ELSE REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\s*(CR|DR)$', ''), '[\$€£¥₹]', ''), ',', '', 'g') END AS DECIMAL(18,2))How finetype reads the samples
DuckDB 1.5.5 · finetype 0.6.66
| Value | As VARCHAR | is_negative |
|---|---|---|
| $1,234.56- | -1234.56 | True |
| 1,234.56 CR | -1234.56 | True |
| $999.99- | -999.99 | True |
| 10,000.00 DR | 10000.00 | False |
Pattern
^[\$€£¥₹]?[0-9]{1,3}(,[0-9]{3})*(\.[0-9]{1,2})?\s?(-|CR|DR)$| Value | Length | Shape |
|---|---|---|
| $1,234.56- | 10 | matches |
| 1,234.56 CR | 11 | matches |
| $999.99- | 8 | matches |
| 10,000.00 DR | 12 | matches |
| $1,234.56 | 9 | rejected: one character short |
Background
Origin
Micro Focus's COBOL documentation allows only one of the editing sign control symbols in a given PICTURE character-string.[1] Micro Focus's COBOL documentation says that when CR or DB is used, the symbols must represent the rightmost character positions counted in the size of the item.[1] In Micro Focus's table of editing symbols, a - in the picture prints a space when the data item is positive or zero and a minus sign when it is negative.[1] Micro Focus's table shows the CR symbol printing as two spaces for a positive or zero item and as CR for a negative one.[1]
Use
SAP's ABAP Programming documentation for the WRITE statement shows a negative packed number output as 1,234,567.89-, with the minus sign after the digits.[2] Oracle Database's MI number format element returns a negative value with a trailing minus sign and a positive value with a trailing blank.[3] Oracle Database allows the MI format element only in the last position of a number format model.[3] PostgreSQL's documentation shows to_char(-485, '999MI') producing '485-' and to_char(485, '999MI') producing '485 ' with a trailing space.[4]
PostgreSQL's documentation gives to_number('12,454.8-', '99G999D9S') as returning -12454.8.[4] Microsoft's Text Import Wizard for Excel has an Advanced option to specify that one or more numeric values may contain a trailing minus sign.[5] Treat a trailing minus as part of the number rather than stray punctuation, and check that a column's count of negative values is the same before and after you convert it. (Meridian's view)
Sources
- [1]The PICTURE Clausedocumentation
- [2]The WRITE Statementdocumentation
- [3]Format Modelsdocumentation
- [4]PostgreSQL: Documentation: 18: 9.8. Data Type Formatting Functionsdocumentation
- [5]Text Import Wizarddocumentation
How it's detected
SAP, COBOL/mainframe, bank statements. CR = credit (negative in expense context), DR = debit.
Schema
https://meridian.online/schemas/finance.currency.amount_neg_trailing · Frictionless type string
finetype taxonomy finance.currency.amount_neg_trailing -o json-schema