number format – Page 6

How to Format a Number with a Comma in Oracle

October 28, 2021September 11, 2021 by Ian

When you use the TO_CHAR() function to format a number in Oracle, you use a format model to determine how the number should be formatted.

For example, you could format a number like 12,345.00 or like 12.345,00, depending on your locale.

The format model can include the G or D format elements to add a comma to a number. Which one you use depends on whether you want the comma as a thousands separator, or as a decimal character.

Alternatively, you can use an actual comma character (,) if you prefer, although this method is not locale aware like the G and D format elements are.

How to Format a Number as Currency in Oracle

September 10, 2021 by Ian

Oracle Database provides the TO_CHAR(number) function that enables you to format numbers based on a given format model. There are three format elements that you can use with the TO_CHAR() function in order to return a value as a currency.

3 Ways to Format a Number to 2 Decimal Places in Oracle

September 10, 2021September 10, 2021 by Ian

When using Oracle Database, you can use functions like TO_CHAR(number) to return numbers as a string, formatted to two decimal places (or however many decimal places you require).

Or you can use functions like ROUND(number) and TRUNC(number) to round or truncate the number to your required number of decimal places.

How to Change your Session’s Currency Symbol in Oracle

September 3, 2021 by Ian

In Oracle Database, there are a few different NLS parameters that determine how currency is displayed when using functions like TO_CHAR() to format a number as a currency.

NLS_CURRENCY specifies the string to use as the local currency symbol for the L number format element.
NLS_ISO_CURRENCY determines what to use for the C format element.
NLS_DUAL_CURRENCY specifies what to use for the U format element.

List of Number Format Elements in Oracle

August 31, 2021 by Ian

Below is a full list of the format elements that can be used to format numbers in Oracle Database.

How FORMAT() Works in MariaDB

April 10, 2021 by Ian

In MariaDB, FORMAT() is a built-in string function that formats and returns the given number as a string.

It requires two arguments, and accepts an optional third argument. The first is the number you want to format, the second is the decimal position, and the (optional) third is the locale.

How to Format Numbers as Currency in PostgreSQL

August 17, 2020 by Ian

To format a number as currency in Postgres, you can either convert it to the money data type, or use to_char() to convert it to text that includes the appropriate currency symbol.

This obviously assumes that the number isn’t already stored using the money type.

Below are examples of each of these options.

How to Create Ordinal Numbers in PostgreSQL

June 18, 2020 by Ian

In PostgreSQL, you can use the to_char() function to output numbers in a given format. This includes appending the number with the ordinal indicator.

For example 1, 2, 3 becomes 1st, 2nd, 3rd.

How to Format Numbers in PostgreSQL

June 14, 2020 by Ian

When working with Postgres, you can use the to_char() function to output numbers in a given format.

The way it works is that you provide two arguments. The first argument is the number to be formatted. The second argument determines how it is formatted.

The return value is text.

Depending on your requirements, you might prefer to convert the number to the appropriate data type, for example using the cast() function.

Examples of both methods are below.

Prepend a Plus/Minus Sign to a Number in PostgreSQL

June 12, 2020 by Ian

In PostgreSQL, you can use the to_char() to output a number in a certain format, including with a plus or minus sign to indicate whether it’s a positive or negative number.

To do this, use one of S, MI, PL, or SG in your format string.

Another option is to use PR in order to enclose any negative values in angle brackets.