Some DBMSs have an LPAD() and RPAD() function which can be used to pad numbers with leading and trailing zeros.
SQL Server doesn’t have such a function. But that doesn’t prevent us from being able to pad numbers with leading/trailing zeros.
Continue readingMariaDB has the FORMAT() function, which formats a number based on the given format string.
It returns the number as a formatted string, with thousands and decimal separators in the appropriate position and rounds the result to the specified decimal position.
You can optionally specify a locale value to format numbers to the pattern appropriate for the given region.
Continue readingIn PostgreSQL, we can use the TO_CHAR() function to format numbers in a given format. This includes formatting numbers with commas in the relevant location.
PostgreSQL also has a money data type, which outputs the value using the current locale. This can include commas in the appropriate place, based on the locale being used.
SQL Server provides us with a quick and easy way to format numbers with commas inserted at the relevant place. For example, 1234.56 can become 1,234.56. Or it can become 1.234,56, if that’s the locale that you’re using.
Continue readingSQLite has a printf() function or format() function that allows us to format numbers according to a format string.
As from SQLite 3.18.0, it accepts a comma flag, which enables us to have comma separators at the thousands marks for integers.
Further work can be done to get it working with real/floating point numbers.
Continue readingSQLite has a PRINTF() function (and now a FORMAT() function) that allows us to format a number based on a format string. For example, we can use this to format the number to a given number of decimal places, plus add a currency symbol to it.
When using the TO_CHAR() function to format a number in Oracle Database, you can use the S format element to return its sign (i.e. a plus sign for positive values, and a minus sign for negative values).
You can also use the MI format element to return negative values with a trailing negative sign, and positive values with a trailing blank.
Here’s an example of formatting a number as currency in MariaDB.
Continue readingSome DBMSs provide us with the ability to format a number as a currency by providing a format specifier for the currency symbol. Providing such a format specifier allows the DBMS to return the appropriate currency symbol for the locale.
MySQL doesn’t have a currency format specifier, and so we need to do a bit of extra work if we want the currency symbol to be returned.
Continue readingIn Oracle Database, you can format numbers in many ways.
For example, you can format a number as a currency, with commas and decimal points in the right place. You can specify leading zeros, you can add a fractional part – or remove it, if that’s what’s required.
Continue reading