Conversion Functions

I l @ ve RuBoard

PostgreSQL includes a number of conversion functions. These are used to convert from one data type to another and to format specific output styles.

CAST

Description

The CAST function can be used to convert from one data type to another. Generally speaking, CAST is a fairly generic and easy-to-use function that makes most data-type conversions easy.

Inputs
 CAST(  value AS newtype  ) 

value ”The value that needs converting.

newtype ”The new data type to convert to.

Examples
 CAST('57' as INT))  57  CAST(57 as CHAR)  '57'  CAST(57 as NUMERIC(4,2))  57.00  CAST('05-23-87' as DATE))  1987-05-23 
Notes

An additional way to perform type conversion is to separate the value and the desired data type with double colons (::).

Then the preceding examples would appear as follows :

 '57'::INT  57  57::CHAR  '57'  57::NUMERIC(4,2)  57.00  '05-23-87'::DATE  1987-05-23 

TO_CHAR

Description

The TO_CHAR function takes various input data types and converts them to a string data type. In addition to performing a data conversion, the TO_CHAR function also has extensive formatting capabilities to output the string in the exact format desired.

Inputs

The TO_CHAR function shares a common usage pattern regardless of the data type it is handling. All TO_CHAR functions accept two arguments; the first is the data to be converted, and the second is a formatting template for PostgreSQL to use when constructing the output.The following table illustrates this usage pattern.

Usage

Description

TO_CHAR(int, texttemplate)

Converts from an integer to a specific string format.

TO_CHAR(numeric, textemplate)

Converts from a numeric to a specific string format.

TO_CHAR(double precision, textemplate)

Converts from a double to a specific string format.

TO_CHAR(timestamp, texttemplate)

Converts from a timestamp to a specific string format.

TO_CHAR with Numbers ( Int, Numeric, or Double Precision )

Converting to a character string from a numerical data type uses the following template mask for formatting output.

(In addition to the following specific formatting commands, the TO_CHAR function will also blindly accept and display any text enclosed in double quotes. This can be very helpful when trying to perform specific labeling of output data.)

Item

Description

Leading zero

9

Digit placeholder

.

Decimal point

,

Thousands separator

G

Group separator*

D

Decimal point*

S

Negative values with minus sign* ( “)

PR

Negative values in angle brackets* (<>)

L

Currency symbol*

MI

Minus sign in specified position (if n<0)

PL

Plus sign in specified position (if n>0)

SG

Plus or minus sign in specified position

RN

Output Roman numeral (for n>1 and n<3999)

TH

Convert to ordinal number

Vn

Shift value by 10*n digits

* These items use the locale setting for your particular machine, so your results might vary.

TO_CHAR with Date/Time Data Types

The TO_CHAR (and TO_DATE, TO_TIMESTAMP ) function uses the following date-time “ related template mask for formatting output:

Item

Description

SSSS

Seconds past midnight (0 “86399)

SS

Second (00 “59)

MI

Minute (00 “59)

HH

Hour of day (01 “12)*

HH12

Hour of day (01 “12)

HH24

Hour of day (00 “23)

AM or A.M.

Meridian indicator (uppercase)

PM or P.M.

Meridian indicator (uppercase)

am or a.m.

Meridian indicator (lowercase)

pm or p.m.

Meridian indicator (lowercase)

DAY

Uppercase full day name (such as MONDAY)

Day

Proper case full day name (such as Monday)

day

Lowercase full day name (such as monday)

DY

Uppercase abbreviated day name (such as MON)

Dy

Proper case abbreviated day name (such as Mon)

dy

Lowercase abbreviated day name (such as mon)

D

Day of the week (1 “7; SUN = 1)

DD

Day of the month (01 “31)

DDD

Day of the year (001 “366)

W

Week of the month (1 “5)

WW

Week of the year (1 “53; first week starts 01/01)

IW

ISO week of year (1 “53; first week starts on first Thursday of Jan)

MM

Month (01 “12)

MONTH

Uppercase full month name (such as JUNE)

Month

Proper case full month name (such as June)

month

Lowercase full month name (such as june)

MON

Uppercase abbreviated month name (such as JUN)

Mon

Proper case abbreviated month name (such as Jun)

mon

Lowercase abbreviated month name (such as jun)

Y

Last digit of year (such as 1)

YY

Last two digits of year (such as 01)

YYY

Last three digits of year (such as 001)

YYYY

Full year (four and more digits) (such as 2001)

Y,YYY

Full year (four and more digits) (such as 2,001)

CC

Century (such as 20)

BC or B.C.

Era indicator (uppercase)

bc or b.c.

Era indicator (lowercase)

AD or A.D.

Era indicator (uppercase)

ad or a.d.

Era indicator (lowercase)

J

Julian day (days since 01/01/4712 BC)

Q

Quarter

RM

Uppercase month in Roman numeral (I = Jan)

rm

Lowercase month in Roman numeral (I = Jan)

TZ

Uppercase time zone

tz

Lowercase time zone

* These items use the locale setting for your particular machine, so your results might vary.

Examples

Numerical and date/time examples are given in the following sections.

TO_CHAR Numerical Examples

Input

Output

TO_CHAR(123,'999')

123

TO_CHAR(123,'99 9')

12 3

TO_CHAR(123,'0999')

0123

TO_CHAR(123,'999.9')

123.0

TO_CHAR(1234,'9,999')

1,234

TO_CHAR(1234,'9G999')

1,234

TO_CHAR(1234.5,'9999D99')

1234.50

TO_CHAR(123,'999PL')

123+

TO_CHAR(123,'PL123')

+123

TO_CHAR(-123,'999MI')

123-

TO_CHAR(-123,'MI123')

-123

TO_CHAR(123,'SG123')

+123

TO_CHAR(-123,'SG123')

-123

TO_CHAR(-123,'999PR')

<123>

TO_CHAR(123,'RN')

CXXIII

TO_CHAR(32, '99TH')

32nd

TO_CHAR(123,'9"Hundred and"99')

1 Hundred and 23

TO_ CHAR Date/Time Examples

Input

Output

TO_CHAR('November 1 2001', 'MM"--"DD"--"YY')

11--01--01

TO_CHAR('Jun 22 2001', '"Year"YYYY "Day"DDD')

Year 2001 Day 174

Notes

Any items in double quotes are ignored. Therefore, to output reserved template words, simply enclose them in double quotes (that is, YYYY outputs as " YYYY" ).

Special characters like backslashes ( \ ) can be achieved by enclosing them in quotes and doubling them (that is," \\ " become " \ " on output).

The preceding templates are used in many other TO -style functions (that is, TO_DATE , TO_NUMBER , and so on).

TO_DATE

Description

The TO_DATE function converts a text string to a date format. The TO_DATE function takes two arguments; the first is the string to be converted, and the second is a text template that specifies how the output is to appear.

Input
 TO_DATE(text, texttemplate) 
Example
 TO_DATE('01 01 2001', 'MONTH DD YYYY')  JANUARY 01 2001 
Notes

There are a number of options that the text template string can take. Refer to TO_CHAR for a full listing of the options that the date-time template can take.

TO_NUMBER

Description

The TO_NUMBER function is used to convert from character input strings to a numeric output.The TO_NUMERIC function accepts two input arguments; the first is the text to be converted, and the second is the text template that specifies how the output format is to appear.

Input
 TO_NUMBER(text, texttemplate) 
Examples
 TO_CHAR(1234567,'9G999G999')  1,234,567  TO_CHAR(1234.5,'9999D99')  1234.50 
Notes

The text template of the TO_NUMBER function accepts a number of options. For a full listing of supported layout options, refer to the TO_CHAR function.

TO_TIMESTAMP

Description

The TO_TIMESTAMP function is used to convert from a string format to a timestamp data type. The TO_TIMESTAMP function accepts two arguments; the first is the string to be converted, and the second is a date-time template used to format the resulting output.

Input
 TO_TIMESTAMP (  text  ,  texttemplate  ) 
Example
 TO_TIMESTAMP('05 December 2001','DD MM YYYY')  12 05 2001 
Notes

The date-time template accepts many options for formatting output. Refer to the TO_CHAR function for a full list of valid date-time formatting options.

I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net