Conversion functions

You can use the Netezza Performance Server SQL formatting functions to convert data types (date/time, integer, floating point, numeric) to formatted strings and to convert from formatted strings to specific data types.

The to_* functions all use a common calling convention: the first argument is the value to be formatted, and the second argument is a template that defines the output or input format. The patterns used by these templates are described in Template patterns for date/time conversions.

Note: The to_char type casts the date data type to a timestamp data type internally.
The following table describes the conversion functions.
Table 1. Conversion functions
Type Description Examples
hex_to_binary(VARCHAR) Converts a hexadecimal character encoded string to its equivalent binary. It returns a VARBINARY value.  
hex_to_geometry(VARCHAR) Converts a hexadecimal character encoded string to its equivalent ST_GEOMETRY value.  
int_to_string(INT8, byteint) Converts a decimal value to a hexadecimal, octal, decimal, or binary string. The function returns a VARCHAR value. The first argument specifies the decimal value to convert and the second argument specifies the base of the number system to which to convert.
  • base 2 = Binary
  • base 8 = Octal
  • base 10 = Decimal
  • base 16 = Hexadecimal
  
string_to_int(VARCHAR, byteint) Converts a hexadecimal, octal, decimal, or binary string to a decimal value. The function returns an INT8 value. The first argument specifies the string to convert and the second argument specifies the base of the number system of the first argument.
  • base 2 = Binary
  • base 8 = Octal
  • base 10 = Decimal
  • base 16 = Hexadecimal
  
to_char(value,template) Converts the specified value (which can be a date, timestamp, real, double precision, or numeric value) to a character string.
  • In the template, text that is enclosed in double quotation marks is passed through.
  • To output a double quotation mark, precede it with a backslash.
  • When converting a positive number to a char, this function inserts a leading blank so that positive and negative values have equal length. To suppress this leading blank, use the FM prefix option.
 See Table 2.
to_number(text,template) Converts a character string to a numeric value. select to_number('12,454.8-', '99G999D9S');

Returns: -12454.8

to_date(text,template) Converts a character string to a date. Skips multiple blank spaces unless you specify the FX prefix as the first item in the template. select to_date('31 Dec 2015','DD Mon YYYY');

Returns: 2015-12-31

select to_date('31 Dec 2015','FXDD Mon YYYY');

Returns: 2015-12-31

select to_date('31 Dec 2015 20:33:33','DD Mon YYYY HH24":"MI":"SS');

Returns: 2015-12-31

to_timestamp(text,template) Converts a character string to a timestamp. Skips multiple blank spaces unless you specify the FX prefix as the first item in the template. select to_timestamp('31 Dec 2015 08:38:40 pm', 'DD Mon YYYY HH:MI:SS am');

Returns: 2015-12-31 20:38:40

Note that the meridian indicator (am or pm) of the template does not need to match that of the input string. However, it must use the same format (with or without periods).

Examples of the to_char function

A SELECT statement that contains one of the expressions shown in the following table returns the indicated result.

Table 2. Examples of the to_char function
Expression Result Comments
to_char(date '2015-02-14', 'YYYY Month') '2013 February ' A trailing blank follows "February" because nine characters (the number of letters in "September") are reserved for the name of a month, regardless of how many letters are actually required.
to_char(date '2015-02-14', 'YYYY FMMonth') '2013 February' The FM prefix removes the trailing blank after "February".
to_char(timestamp '2015-02-14 20:19:07', 'HH24:MI:SS') '20:19:07'  
to_char(timestamp '2015-02-14 20:19:07', 'HH:MI:SS am') '08:19:07 pm' The meridian indicator specified by the template is adjusted in the output to correspond to the hour (am for hours from 0-12 or pm for hours from 12-23). The format of the meridian indicator specified by the template (uppercase or lowercase, with or without periods) is used for the output.
to_char(timestamp '2015-02-14 20:19:07', 'Day, DD  HH:MI:SS am') 
'Monday   , 04 08:19:07 pm'
 Three trailing blanks follow "Monday" because nine characters (the number of letters in "Wednesday") are reserved for the name of a day of the week, regardless of how many letters are actually required.
to_char(timestamp '2015-02-14 20:19:07', 'FMDay, DD  HH:MI:SS am') 
'Monday, 4 8:19:7 pm'
 The FM prefix removes the trailing blanks that follow "Monday". It also removes the leading zeros from the date, hours, and seconds.
to_char(timestamp '2015-02-14 20:19:07', 'Day, DD  FMHH:MI:SS am') 
'Monday   , 04 8:19:7 pm'
 The FM prefix removes the leading zeros from the hours and seconds.
to_char(125, '999') ' 125' The result is a string of 4 characters, one for each position in the template, plus an additional blank that precedes the number in lieu of a plus (+) sign.
to_char(-125, '999') '-125' There is no blank preceding the number, due to the minus (-) sign.
to_char(125, '"999D999') ' 125.000' The template specifies that the output is to include three decimal places. The result is a string of 8 characters, one for each position in the template (the decimal delimiter counts as a place in the template), plus an additional blank that precedes the number in lieu of a plus (+) sign.
to_char(-125, '"The number is "999"."') 'The number is -125.' The text in double quotation marks is passed through.
to_char(125, '"The number is "FM999"."') The number is 125. The FM prefix suppresses the extra blank that would normally precede the positive number.
to_char(12.7, '99.9') ' 12.7' The result is a string of 5 characters, one for each position in the template, plus an additional blank that precedes the number in lieu of a plus (+) sign.
to_char(12.7, 'FM99.9') '12.7' The FM prefix suppresses the extra blank that would normally precede the positive number.
to_char(0.1, '999.99') 
'    .10'
 The result is a string of 7 characters, one for each position in the template. (The decimal point counts as a place in the template.)
to_char(-0.1, '999.99') 
'   -.10'
 The result is a string of 7 characters, one for each position in the template. (The decimal point counts as a place in the template.)
to_char(-0.1, 'FM999.99') '-.1' The FM prefix suppresses the leading blanks and the trailing zero.
to_char(.1, '0.9') ' 0.1' The 0 in the template specifies that as many leading zeros are to be added as there are unused positions in the template. The 0 counts as one position in the template.
to_char(-12.7, '0999.9') '-0012.7' The 0 in the template specifies that as many leading zeros are to be added as there are unused positions in the template. The 0 counts as one position in the template.
to_char(12.7, '90999.9') 
'  0012.7'
 The result is a string of 8 characters, one for each position in the template, plus an additional blank that precedes the number in lieu of a plus (+) sign.
to_char(-12.7, '9999999') 
'     -13'
 The result is a string of 8 characters, one for each position in the template, plus an additional character for the minus (-) sign.
to_char(485, '9 9 9') ' 4 8 5'  
to_char(1485, '9,999') ' 1,485'  
to_char(1485, '9G999') ' 1,485' The G pattern represents the group delimiter specified for your locale.
to_char(148.5, '999.999') ' 148.500'  
to_char(148.5, 'FM999.999') '148.5'  
to_char(148.5, 'FM999.990') '148.500'  
to_char(148.5, '999D999') ' 148.500' Which decimal delimiter is used depends on your locale.
to_char(3148.5, '9G999D999') ' 3,148.500' The G pattern represents the group delimiter specified for your locale.
to_char(125,'S999') '+125' There is no blank preceding the number, because the S pattern specifies that the sign is always to be included in the output.
to_char(125, '999S') '125+' There is no blank preceding the number, because the S pattern specifies that the sign is always to be included in the output.
to_char(-485, '999S') '485-'  
to_char(-485, 'MI999') '-485' For a negative number, the MI pattern places the minus (-) sign in the corresponding position.
to_char(-485, '999MI') '485-'
to_char(485, 'MI999') ' 485' For a positive number, the MI pattern places a blank in the corresponding position. The MI pattern does not affect the leading blank that precedes a positive number.
to_char(485, '999MI') ' 485 '
to_char(485, 'FM999MI') '485 ' The FM prefix suppresses the extra blank that would normally precede the positive number, but does not affect the trailing blank added by the MI suffix.
to_char(485, 'PL999') '+485' For a positive number, the PL pattern places the plus (+) sign in the corresponding position.
to_char(485, '999PL') '485+'
to_char(-485, '999PL') '-485 ' For a negative number, the PL pattern places a blank in the corresponding position. The PL suffix does not affect the minus (-) sign that precedes a negative number.
to_char(-485, 'PL999') ' -485 '
to_char(-3485, 'SG9999') '-3485'  
to_char(-3485, 'S9G999') '-3,485'  
to_char(-3485, '99SG99') '34-85'  
to_char(-485, '999PR') '<485>'  
to_char(485, '999PR') ' 485'  
to_char(485, 'FM999PR') '485' The FM prefix suppresses the extra blank that would normally precede a positive number.
to_char(485, 'RN') 'CDLXXXV'  
to_char(5.2, 'RN') 'V' Roman numerals ignore decimal fractions.
to_char(482, '999th') ' 482nd' A th suffix results in the appropriate ordinal suffix (st, nd, rd, or th).
to_char(485.8, '"Pre: "FM999" Post:" .999') 'Pre: 485 Post: .800' The FM prefix suppresses the extra blank that would normally precede a positive number.
to_char(485.8, '"Pre: "FM999" Post:" .999') 'Pre: -485 Post: .800' The FM prefix does not affect a negative number.
to_char(12, '99V999') ' 12000'  
to_char(12.4, '99V999') ' 12400'  
to_char(12.45, '99V9') ' 125'