VARCHAR_FORMAT scalar function
The VARCHAR_FORMAT function returns a character representation of an input expression.
Character string to VARCHAR
DATE or TIMESTAMP to VARCHAR
Signed numeric to VARCHAR
The schema is SYSIBM.
If any argument of the VARCHAR_FORMAT function can be null, the result can be null; if any argument is null, the result is the null value.
Character string to VARCHAR
- character-expression
- An expression that returns a value that must be a built-in CHAR or VARCHAR data type. In a Unicode database, if a supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function.
The result is a VARCHAR with a length attribute that matches the length attribute of the argument. The value of the result is the same as the value of character-expression.
The code page of the result is the code page of the section.
DATE or TIMESTAMP to VARCHAR
- date-or-timestamp-expression
- An expression that returns a value that must be a DATE or TIMESTAMP, or a valid string
representation of a date or timestamp that is not a CLOB or DBCLOB. In a Unicode database, if the
expression returns a graphic string representation of a date or timestamp, the returned value is
first converted to a character string before the function is evaluated.If the input expression returns:
- A string, the format-string argument must also be specified.
- A DATE or a string representation of a date, the returned value is first converted to a TIMESTAMP(0) value with a time component of exactly midnight (00.00.00).
- A string representation of a timestamp, it is first converted to a TIMESTAMP(12) value
For a list of valid formats for string representations of datetime values, see String representations of datetime values.
- format-string1
- An
expression that returns a value with one of the following built-in data types:
- CHAR or VARCHAR
- Signed numeric
- Datetime
- GRAPHIC or VARGRAPHIC (Unicode databases only)
The default format string is based on the value of the special register CURRENT LOCALE LC_TIME.
A valid format string must contain a combination of the format elements listed in Table 1 (SQLSTATE 22007). Two format elements can optionally be separated by one or more of the following separator characters:- minus sign (-)
- period (.)
- slash (/)
- comma (,)
- apostrophe (')
- semi-colon (;)
- colon (:)
- blank ( )
Table 1. Format elements for DATE or TIMESTAMP to VARCHAR Format element Description AM or PM Meridian indicator (morning or evening) without periods. This format element is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. A.M. or P.M. Meridian indicator (morning or evening) with periods. This format element uses the exact strings 'A.M.' or 'P.M.' and is independent of the locale name in effect. CC Century (01-99). If the last two digits of the four-digit year are zero, the result is the first two digits of the year; otherwise, the result is the first two digits of the year plus one. DAY, Day, or day Name of the day in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. DY, Dy, or dy Abbreviated name of the day in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. D Day of the week (1-7). The first day of the week is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. DD Day of month (01-31). DDD Day of year (001-366). FF or FFn Fractional seconds (0-999999999999), where n specifies the scale of the returned value. Valid values for n are 1 - 12 with no leading zeros. Specifying FF is equivalent to specifying FF6. If the scale of the input timestamp is less than n, the result is padded with trailing zeros. HH HH behaves the same as HH12. HH12 Hour of the day (01-12) in 12-hour format. HH24 Hour of the day (00-24) in 24-hour format. I ISO year (0-9). The last digit of the year based on the ISO week that is returned. ID ISO day of the week (1-7). 1 is Monday and 7 is Sunday. IW ISO week of the year (01-53). The week starts on Monday and includes seven days. Week 1 is the first week of the year to contain a Thursday, which is equivalent to the first week of the year to contain January 4. IY ISO year (00-99). The last two digits of the year based on the ISO week that is returned. IYY ISO year (000-999). The last three digits of the year based on the ISO week that is returned. IYYY ISO year (0000-9999). The 4-digit year based on the ISO week that is returned. J Julian day (number of days since January 1, 4713 BC). MI Minute (00-59). MM Month (01-12). MONTH, Month, or month Name of the month in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. MON, Mon, or mon Abbreviated name of the month in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME. MS Milleseconds (000-999). Same as FF3. NNNNNN Microseconds (000000-999999). Same as FF6. Q Quarter (1-4), where the months January through March return 1. RR RR behaves the same as YY. RRRR RRRR behaves the same as YYYY. SS Seconds (00-59). SSSSS Seconds since previous midnight (00000-86400). US Microseconds (000000-999999). Same as FF6. W Week of the month (1-5), where week 1 starts on the first day of the month and ends on the seventh day. WW Week of the year (01-53), where week 1 starts on January 1 and ends on January 7. Y Last digit of the year (0-9). YY Last two digits of the year (00-99). YYY Last three digits of the year (000-999). YYYY 4-digit year (0000-9999). The format elements in Table 1 are not case sensitive, with the following exceptions:- AM, PM
- A.M., P.M.
- DAY, Day, day
- DY, Dy, dy
- D
- MONTH, Month, month
- MON, Mon, mon
In cases where format elements are ambiguous, the case insensitive format elements will be considered first. For example, 'DDYYYY' would be interpreted as "DD followed by YYYY", not "D followed by DY followed by YYY".
- locale-name
- A character constant that specifies the locale used for the following format elements:
- AM, PM
- DAY, Day, day
- DY, Dy, dy
- D
- MONTH, Month, month
- MON, Mon, mon
The specified locale name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information about valid locales and their naming, see
Locale names for SQL and XQuery
. The default is the value of the CURRENT LOCALE LC_TIME special register.
The result is a representation of the input timestamp expression in the format specified by the format string. The format string is interpreted as a series of format elements that can be separated by one or more separator characters. A string of characters in the format string is interpreted as the longest matching format element in Table 1. If two format elements that contain the same characters are not delimited by a separator character, the specification is interpreted, starting from the left, as the longest matching format element in the table, and continues until matches are found for the remainder of the format string. For example, 'YYYYYYDD' is interpreted as the format elements 'YYYY', 'YY', and 'DD'.
The result is a varying-length character string. The length attribute is 255. If the string units of the environment or format-string is CODEUNITS32, the string units of the result is CODEUNITS32. Otherwise, the string units of the result is OCTETS. The format-string determines the actual length of the result. If the resulting string exceeds the length attribute of the result, the result is truncated.
The code page of the result is the code page of the section.
Signed numeric to VARCHAR
- numeric-expression
- An expression that returns a value of any built-in signed numeric data type. If the data type of the value is not DECFLOAT, it is converted to DECFLOAT(34) for processing.
- format-string2
- An expression that returns a value that has one of the following built-in data types:
- CHAR or VARCHAR
- Signed numeric
- Datetime
- GRAPHIC or VARGRAPHIC (Unicode databases only)
If a format string is not specified, the function is equivalent to VARCHAR(DECFLOAT(numeric-expression)).
Table 2. Format elements for decimal floating-point to varchar Format element Description 0 Each 0 represents a significant digit. Leading zeros in a number are displayed as zeros. 9 Each 9 represents a significant digit. Leading zeros in a number are displayed as blanks. PL or pl If the input decimal floating-point expression returns a positive number, a plus sign (+) is added at the specified position. G or g The group separator specified by the locale is added at the specified position. D or d The decimal delimiter specified by the locale is added at the specified position. , A comma is added at the specified position, for example as a group separator. . A period is added at the specified position, for example as a decimal point. S or s Prefix: If the input decimal floating-point expression returns: - A negative number, a leading minus sign (-) is added to the result
- A positive number, a leading plus sign (+) is added to the result
$ Prefix: A leading dollar sign ($) is added to the result. MI or mi Suffix: If the input decimal floating-point expression returns: - A negative number, a trailing minus sign (-) is added to the result
- A positive number, a trailing blank is added to the result
PR or pr Suffix: If the input decimal floating-point expression returns: - A negative number, a leading less than character (<) and a trailing greater than character (>) are added to the result
- A positive number, a leading space and a trailing space are added to the result
The format elements are case sensitive.
- locale-name
- A character constant that specifies the locale used to determine the group separator and decimal
delimiter.
The specified locale name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information about valid locales and their naming, see
Locale names for SQL and XQuery
. The default is the value of the CURRENT LOCALE LC_TIME special register.
The result is a varying-length character string representation of the input decimal floating-point expression. If a single argument is specified the length attribute is 42. Otherwise the length attribute is 254. If the string units of the environment or the format string is CODEUNITS32, the string units of the result is CODEUNITS32; otherwise, the string units of the result is OCTETS. The actual length of the result is determined by the format string, if specified; otherwise, the actual length of the result is the smallest number of characters that can represent the value of the input decimal floating-point expression. If the resulting string exceeds the length attribute of the result, the result is truncated.
- Infinity, the string "INFINITY" is returned
- sNaN, the string "SNAN" is returned
- NaN, the string "NAN" is returned
If the format string does not include any of the format elements MI, S, or PR, and if the value of the input expression is negative, then a minus sign (-) is included in the result; otherwise, a blank is included in the result.
If the number of digits to the left of the decimal point in the input expression is greater than the number of digits to the left of the decimal point in the format string, the result is a string of one or more number sign (#) characters. If the number of digits to the right of the decimal point in the input expression is greater than the number of digits to the right of the decimal point in the format string, the result is rounded to the number of digits to the right of the decimal point in the format string. The DECFLOAT rounding mode will not be used. The rounding behavior of VARCHAR_FORMAT corresponds to a value of ROUND_HALF_UP.
The code page of the result is the code page of the section.
Notes
- Julian and Gregorian calendar: For Timestamp to varchar, the transition from the Julian calendar to the Gregorian calendar on 15 October 1582 is taken into account by this function.
- Determinism: VARCHAR_FORMAT is
a deterministic function. However, the following invocations of the
function depend on the value of the special register CURRENT LOCALE
LC_TIME.
- Timestamp to varchar, when format-string is not explicitly specified,
or when locale-name is not explicitly specified and one of the following statements
is true:
- format-string is not a constant
- format-string is a constant and includes format elements that are locale sensitive
- Timestamp to varchar, when format-string is not explicitly specified,
or when locale-name is not explicitly specified and one of the following statements
is true:
- Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.
Examples
- Example 1: Display the names and creation timestamps for all system tables that have
names that start with SYSU.
This example returns the following output:SELECT VARCHAR(TABNAME, 20) AS TABLE_NAME, VARCHAR_FORMAT(CREATE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS CREATION_TIME FROM SYSCAT.TABLES WHERE TABNAME LIKE 'SYSU%'TABLE_NAME CREATION_TIME -------------------- ------------------- SYSUSERAUTH 2000-05-19 08:18:56 SYSUSEROPTIONS 2000-05-19 08:18:56 - Example 2: The variable TMSTMP is defined as a TIMESTAMP and
has the value 2007-03-09-14.07.38.123456. The following examples show invocations of the
VARCHAR_FORMAT function and the resulting string values. The data type of each result is
VARCHAR(255).
Function invocation Result ------------------- ------ VARCHAR_FORMAT(TMSTMP,'YYYYMMDDHHMISSFF3') 20070309020738123 VARCHAR_FORMAT(TMSTMP,'YYYYMMDDHH24MISS') 20070309140738 VARCHAR_FORMAT(TMSTMP,'YYYYMMDDHHMI') 200703090207VARCHAR_FORMAT(TMSTMP,'HH12:MI:SS.MS AM') 02:07:38.123 PM VARCHAR_FORMAT(TMSTMP,'HH24:MI:SS.US') 14:07:38.123456VARCHAR_FORMAT(TMSTMP,'DD/MM/YY') 09/03/07 VARCHAR_FORMAT(TMSTMP,'MM-DD-YYYY') 03-09-2007 VARCHAR_FORMAT(TMSTMP,'J') 2454169 VARCHAR_FORMAT(TMSTMP,'Q') 1 VARCHAR_FORMAT(TMSTMP,'W') 2 VARCHAR_FORMAT(TMSTMP,'IW') 10 VARCHAR_FORMAT(TMSTMP,'WW') 10 VARCHAR_FORMAT(TMSTMP,'Month','en_US') March VARCHAR_FORMAT(TMSTMP,'MONTH','en_US') MARCH VARCHAR_FORMAT(TMSTMP,'MON','en_US') MAR VARCHAR_FORMAT(TMSTMP,'Day','en_US') Friday VARCHAR_FORMAT(TMSTMP,'DAY','en_US') FRIDAY VARCHAR_FORMAT(TMSTMP,'Dy','en_US') Fri VARCHAR_FORMAT(TMSTMP,'Month','de_DE') März VARCHAR_FORMAT(TMSTMP,'MONTH','de_DE') MÄRZ VARCHAR_FORMAT(TMSTMP,'MON','de_DE') MÄRZ VARCHAR_FORMAT(TMSTMP,'Day','de_DE') Freitag VARCHAR_FORMAT(TMSTMP,'DAY','de_DE') FREITAG VARCHAR_FORMAT(TMSTMP,'Dy','de_DE') Fr. - Example 3: The variable DTE is defined as a
DATE and has the following value: 2007-03-09. The following examples show several invocations of the
function and the resulting string values. The data type of each result is
VARCHAR(255).
Function invocation Result ------------------- ------ VARCHAR_FORMAT(DTE,'YYYYMMDD') 20070309 VARCHAR_FORMAT(DTE,'YYYYMMDDHH24MISS') 20070309000000 - Example 4: The variables POSNUM and NEGNUM are both defined as
DECFLOAT(34), and the value of POSNUM is 1234.56 and the value of NEGNUM is -1234.56. The following
examples show several invocations of the VARCHAR_FORMAT and the resulting string values. The data
type of the first two results is VARCHAR(42) and the rest are
VARCHAR(254).
Function invocation Result ------------------- ------ VARCHAR_FORMAT(POSNUM) '1234.56' VARCHAR_FORMAT(NEGNUM) '-1234.56' VARCHAR_FORMAT(POSNUM,'9999.99') ' 1234.56' VARCHAR_FORMAT(NEGNUM,'9999.99') '-1234.56' VARCHAR_FORMAT(POSNUM,'99999.99') ' 1234.56' VARCHAR_FORMAT(NEGNUM,'99999.99') ' -1234.56' VARCHAR_FORMAT(POSNUM,'00000.00') ' 01234.56' VARCHAR_FORMAT(NEGNUM,'00000.00') '-01234.56' VARCHAR_FORMAT(POSNUM,'9999.99MI') '1234.56 ' VARCHAR_FORMAT(NEGNUM,'9999.99MI') '1234.56-' VARCHAR_FORMAT(POSNUM,'S9999.99') '+1234.56' VARCHAR_FORMAT(NEGNUM,'S9999.99') '-1234.56' VARCHAR_FORMAT(POSNUM,'9999.99PR') ' 1234.56 ' VARCHAR_FORMAT(NEGNUM,'9999.99PR') '<1234.56>' VARCHAR_FORMAT(POSNUM,'S$9,999.99') '+$1,234.56' VARCHAR_FORMAT(NEGNUM,'S$9,999.99') '-$1,234.56'VARCHAR_FORMAT(POSNUM,'99,99,99') ' 12,35' VARCHAR_FORMAT(NEGNUM,'99,99,99') ' -12,35' VARCHAR_FORMAT(POSNUM,'PL9999.99') '+1234.56' VARCHAR_FORMAT(NEGNUM,'PL9999.99') ' 1234.56' VARCHAR_FORMAT(POSNUM,'9999PL') ' 1234+' VARCHAR_FORMAT(NEGNUM,'9999PL') '-1234 ' VARCHAR_FORMAT(POSNUM,'9999.9') ' 1234.6' VARCHAR_FORMAT(NEGNUM,'9999.9') '-1234.6' VARCHAR_FORMAT(POSNUM,'9999') ' 1235' VARCHAR_FORMAT(NEGNUM,'9999') '-1235' VARCHAR_FORMAT(POSNUM,'99.99') '######' VARCHAR_FORMAT(NEGNUM,'99.99') '######' VARCHAR_FORMAT(POSNUM,'9999D99', 'en_US') ' 1234.56' VARCHAR_FORMAT(POSNUM,'9999D99', 'fr_FR') ' 1234,56' VARCHAR_FORMAT(POSNUM,'9G999D99', 'en_US') ' 1,234.56' VARCHAR_FORMAT(POSNUM,'9G999D99', 'de_DE') ' 1.234,56'