SI-Prefix Number

representation.numeric.si_number

Human-readable number with SI/business notation suffix indicating magnitude. Common in dashboards, reports, and financial summaries. K=thousands, M=millions, B=billions, T=trillions. May include optional currency prefix ($, €, £) or sign (+/-).

Domain

representation

Try it

CLI

$ finetype infer -i "12.2K"
→ representation.numeric.si_number

DuckDB

Detect

SELECT finetype('12.2K');
-- → 'representation.numeric.si_number'

Cast expression

CASE
  WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('K','k')
    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Kk]$', '') AS DOUBLE) * 1000
  WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('M','m')
    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Mm]$', '') AS DOUBLE) * 1000000
  WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('B','b')
    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Bb]$', '') AS DOUBLE) * 1000000000
  WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('T','t')
    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Tt]$', '') AS DOUBLE) * 1000000000000
END

Safe cast pipeline

-- Normalise and cast in one step
SELECT TRY_CAST(finetype_cast(my_column) AS DOUBLE) AS clean_value
FROM my_table
WHERE finetype(my_column) = 'representation.numeric.si_number';

JSON Schema

finetype schema representation.numeric.si_number

{
  "$id": "https://meridian.online/schemas/representation.numeric.si_number",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "description": "Human-readable number with SI/business notation suffix indicating magnitude. Common in dashboards, reports, and financial summaries. K=thousands, M=millions, B=billions, T=trillions. May include optional currency prefix ($, €, £) or sign (+/-).",
  "examples": [
    "12.2K",
    "1.5M",
    "2.3B",
    "$500K",
    "-1.2M"
  ],
  "pattern": "^[\\$€£+-]?\\d+\\.?\\d*[KkMmBbTt]$",
  "title": "SI-Prefix Number",
  "type": "string",
  "x-finetype-broad-type": "DOUBLE",
  "x-finetype-transform": "CASE\n  WHEN regexp_extract(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[KkMmBbTt]$') IN ('K','k')\n    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[Kk]$', '') AS DOUBLE) * 1000\n  WHEN regexp_extract(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[KkMmBbTt]$') IN ('M','m')\n    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[Mm]$', '') AS DOUBLE) * 1000000\n  WHEN regexp_extract(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[KkMmBbTt]$') IN ('B','b')\n    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[Bb]$', '') AS DOUBLE) * 1000000000\n  WHEN regexp_extract(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[KkMmBbTt]$') IN ('T','t')\n    THEN CAST(regexp_replace(regexp_replace({col}, '^[\\\\$€£+-]', ''), '[Tt]$', '') AS DOUBLE) * 1000000000000\nEND\n"
}

Examples

12.2K1.5M2.3B$500K-1.2M

Aliases

business_notationhuman_readable_numberabbreviated_number