📣
TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →
​
Sign InStart for Free
AI
Developer
Best Practices
API
​
Sign InStart for Free

TO_DECIMAL

Converts a value to DECIMAL data type.

Syntax

TO_DECIMAL
( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

Arguments

ArgumentsDescription
exprAn expression of a boolean, number, string, or variant type.
formatOptional. A format string specifying the desired precision and scale for the decimal result. It may define the total number of digits and the number of digits after the decimal point.
precisionOptional. Total number of significant digits in the result, the default is 38.
scaleOptional. Number of digits after the decimal point. Must not exceed precision, the default is 0.

Format Specifiers

Format SpecifierDescriptionExample Input
'9'Optional digit (accepts digit or nothing, but this implementation requires one)"123"
'0'Required digit (same as 9 in current implementation)"123"
'D'Decimal point (accepts '.' or ',', always normalized to '.')"12.34"
'G'Group (thousands) separator (expects ',' in input)"1,234"
'S'Sign ('+' or '-', applied as prefix to result)"-123"
Literal characterAny literal character must appear in the input exactly"NT$123"

Examples

SELECT TO_DECIMAL('1234.56');

╭───────────────────────╮
│ to_decimal('1234.56') │
├───────────────────────┤
│                  1235 │
╰───────────────────────╯

SELECT TO_DECIMAL('1234.56', '9999D99');

╭──────────────────────────────────╮
│ to_decimal('1234.56', '9999D99') │
├──────────────────────────────────┤
│                             1235 │
╰──────────────────────────────────╯

SELECT TO_DECIMAL('1234.56', 38, 1);

╭──────────────────────────────╮
│ to_decimal('1234.56', 38, 1) │
├──────────────────────────────┤
│                       1234.6 │
╰──────────────────────────────╯

SELECT TO_DECIMAL('NT$1234.56', 'NT$9999D99', 38, 1);

╭───────────────────────────────────────────────╮
│ to_decimal('NT$1234.56', 'NT$9999D99', 38, 1) │
├───────────────────────────────────────────────┤
│                                        1234.6 │
╰───────────────────────────────────────────────╯

Was this page helpful?