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. CASTDescriptionThe 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. ExamplesCAST('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 NotesAn 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_CHARDescriptionThe 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. InputsThe 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.
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.)
* 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:
* These items use the locale setting for your particular machine, so your results might vary. ExamplesNumerical and date/time examples are given in the following sections. TO_CHAR Numerical Examples
TO_ CHAR Date/Time Examples
NotesAny 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_DATEDescriptionThe 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. InputTO_DATE(text, texttemplate) ExampleTO_DATE('01 01 2001', 'MONTH DD YYYY') JANUARY 01 2001 NotesThere 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_NUMBERDescriptionThe 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. InputTO_NUMBER(text, texttemplate) ExamplesTO_CHAR(1234567,'9G999G999') 1,234,567 TO_CHAR(1234.5,'9999D99') 1234.50 NotesThe 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_TIMESTAMPDescriptionThe 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. InputTO_TIMESTAMP ( text , texttemplate ) ExampleTO_TIMESTAMP('05 December 2001','DD MM YYYY') 12 05 2001 NotesThe 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 |