PostgreSQL to_char (numeric) with practical examples

PosgreSQL to_char is one of the most used formatting function

There are two broad category for to_char in PostgreSQL ,TO_CHAR with numeric data type and TO_CHAR with date/time data type. In this post we will examine TO_CHAR with numeric data type

TO_CHAR with numeric data type

select to_char(2014,’9999′); → ‘2014’, i.e to_char convert integer 2014 to string 2014

select to_char(15,’99’); → ’15’, i.e to_char convert integer 15 to string 15

to_char ( numeric_type, pattern) → text , we can use below Patterns for Numeric Formatting

PatternDescription
9digit position (can be dropped if insignificant)
0digit position (will not be dropped, even if insignificant)
. (period)decimal point
, (comma)group (thousands) separator
PRnegative value in angle brackets
Ssign anchored to number (uses locale)
Lcurrency symbol (uses locale)
Ddecimal point (uses locale)
Ggroup separator (uses locale)
MIminus sign in specified position (if number < 0)
PLplus sign in specified position (if number > 0)
SGplus/minus sign in specified position
RNRoman numeral (input between 1 and 3999)
TH or thordinal number suffix
Vshift specified number of digits (see notes)
EEEEexponent for scientific notation

Some more examples

select to_char(125, ‘999’);‘999’
select to_char(125.8::real, ‘999D9’);125.8
select to_char(-125.8, ‘999D99S’);125.80-
select to_char(-0.1, ‘99.99’);-.10
select to_char(11.1, ‘99.99’);11.10
select to_char(12, ‘0999’);0012
select to_char(2, ‘0999’);0002
select to_char(12564, ‘999,99’);125,64
select to_char(1256, ‘999,99’);12,56
select to_char(123, ‘9 9 9’);1 2 3
select to_char(1234, ‘9G999’);1,234
select to_char(0.1, ‘0.9’);0.1
select to_char(123.5, ‘FM999.999’); 123.5
select to_char(123, ‘L999’);$ 123
select to_char(123.5, ‘FM999.990’);123.500
select to_char(-123, ‘999S’);123-
select to_char(-123, ‘999MI’);123-
select to_char(148.5, ‘999D999’);123.500
to_char(3148.5, ‘9G999D999’); 1,234.500
select to_char(123, ‘PL999’);+ 123
select to_char(123, ‘SG999’);+123
select to_char(-123, ‘9SG99’);1-23
select to_char(-123, ‘999PR’);<123>
select to_char(123, ‘RN’);CXXIII
select to_char(123, ‘FMRN’);CXXIII
select to_char(53, ‘FMRN’);LIII
select to_char(123, ‘999th’);123rd
select to_char(585, ‘999th’);585th
select to_char(585, ‘999TH’);585TH
select to_char(123, ‘”Good number:”999’);Good number: 123
select to_char(12, ’99V999′);12000
select to_char(12, ’99V999′); 12400
select to_char(0.0001234, ‘9.99EEEE’);1.23e-04
select to_char(12.45, ’99V9′); 125

In another post we will examine to_char with date/time data time 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: