DB2中的VARCHAR_FORMAT函数
VARCHAR_FORMAT scalar function
Character to varchar
Timestamp to varchar:
>>-VARCHAR_FORMAT--(--timestamp-expression--+--------------------------------------+--)->< '-,--format-string--+----------------+-' '-,--locale-name-'
Decimal floating-point to varchar:
The schema is SYSIBM.
The VARCHAR_FORMAT function returns a character string based on applying the specified format string argument, if provided, to the value of the first argument. 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.
The expression must be formatted according to a specified character template.
- Character 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.
- Timestamp to varchar
-
- 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. If the argument is a string, the format-string argument must also be specified. In a Unicode database, if a supplied argument is a graphic string representation of a data, time, or timestamp, it is first converted to a character string before evaluating the function.
If timestamp-expression is a DATE or a valid string representation of a date, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).
For the valid formats of string representations of datetime values, see "String representations of datetime values" in "Datetime values".
- format-string
- The expression must return a value that is a built-in CHAR, VARCHAR, numeric, or datetime data type. If the value is not a CHAR or VARCHAR data type, it is implicitly cast to VARCHAR before evaluating the function. In a Unicode database, if the supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function. The actual length must not be greater than 254 bytes (SQLSTATE 22007). The value is a template for how timestamp-expression is to be formatted.
A valid format-string must contain a combination of the format elements listed below (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 the VARCHAR_FORMAT function 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). The number n is used to specify the number of digits to include in the returned value. Valid values for n are 1-12 with no leading zeros. Specifying FF is equivalent to specifying FF6. If the timestamp precision of timestamp-expression is less than what is specified by the format, zero digits are padded onto the right of the specified digits. 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. 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. I ISO year (0-9). The last digit of the year based on the ISO week that is returned. 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). NNNNNN Microseconds (000000-999999). Same as FF6. 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. 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). 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). Note: The format elements in Table 1 are not case sensitive, except for the following:- 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, rather than D followed by DY, followed by YYY.
If format-string is not specified, the format used is based on the value of the special register CURRENT LOCALE LC_TIME.
- 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 value of locale-name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information on valid locales and their naming, see "Locale names for SQL and XQuery". If locale-name is not specified, the value of the special register CURRENT LOCALE LC_TIME is used.
The result is a representation of timestamp-expression in the format specified by format-string. The format-string is interpreted as a series of format elements that can optionally be separated by one or more separator characters. A string of characters in format-string is interpreted as the longest matching format element in Table 1. If two format elements containing 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 254. 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.
- Decimal floating-point to varchar
-
- decimal-floating-point-expression
- An expression that returns a value of any built-in numeric data type. If the argument is not a decimal floating-point value, it is converted to DECFLOAT(34) for processing.
- format-string
- The expression must return a value that is a built-in CHAR, VARCHAR, numeric, or datetime data type. If the value is not a CHAR or VARCHAR data type, it is implicitly cast to VARCHAR before evaluating the function. In a Unicode database, if the supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function. The actual length must not be greater than 254 bytes (SQLSTATE 22018). The value is a template for how decimal-floating-point-expression is to be formatted. Format elements specified as a prefix can be used only at the beginning of the template. Format elements specified as a suffix can be used only at the end of the template. The format elements are case sensitive. The template must not contain more than one of the MI, S, or PR format elements (SQLSTATE 22018).
Table 2. Format elements for the VARCHAR_FORMAT function 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. MI Suffix: If decimal-floating-point-expression is a negative number, a trailing minus sign (−) is included in the result. If decimal-floating-point-expression is a positive number, a trailing blank is included in the result. S Prefix: If decimal-floating-point-expression is a negative number, a leading minus sign (−) is included in the result. If decimal-floating-point-expression is a positive number, a leading plus sign (+) is included in the result. PR Suffix: If decimal-floating-point-expression is a negative number, a leading less than character (<) and a trailing greater than character (>) are included in the result. If decimal-floating-point-expression is a positive number, a leading space and a trailing space are included in the result. $ Prefix: A leading dollar sign ($) is included in the result. , Specifies that a comma be included in that location in the result. This comma is used as a group separator. . Specifies that a period be included in that location in the result. This period is used as a decimal point. If format-string is not specified, decimal-floating-point-expression is formatted in the form of an SQL decimal floating-point constant. If decimal-floating-point-expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If decimal-floating-point-expression is zero, the result is 0.
The result is a varying-length character string representation of decimal-floating-point-expression. The length attribute is 254. 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 decimal-floating-point-expression. If the resulting string exceeds the length attribute of the result, the result will be truncated.
If the value of decimal-floating-point-expression is the special value Infinity, sNaN, or NaN, the strings "INFINITY", "SNAN", and "NAN", respectively, are returned. If the special value is negative, the first character of the result is a minus sign. The decimal floating-point special value sNaN does not result in an exception when converted to a string.
If format-string does not include any of the format elements MI, S, or PR, and the value of decimal-floating-point-expression is negative, then a minus sign (−) will be included in the result; otherwise, a blank will be included in the result.
If the number of digits to the left of the decimal point in decimal-floating-point-expression is greater than the number of digits to the left of the decimal point in format-string, the result is a string of number sign (#) characters. If the number of digits to the right of the decimal point in decimal-floating-point-expression is greater than the number of digits to the right of the decimal point in format-string, the result is decimal-floating-point-expression rounded to the number of digits to the right of the decimal point in 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 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 is true:
- Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.
Examples
- Display the table names and creation timestamps for all of the system tables whose name starts with 'SYSU'.
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%'This example returns the following:TABLE_NAME CREATION_TIME -------------------- ------------------- SYSUSERAUTH 2000-05-19 08:18:56 SYSUSEROPTIONS 2000-05-19 08:18:56
- Assume that the variable TMSTAMP is defined as a TIMESTAMP and has the following value: 2007-03-09-14.07.38.123456. The following examples show several invocations of the function and the resulting string values. The result data type in each case is VARCHAR(254).
Function invocation Result ------------------- ------ VARCHAR_FORMAT(TMSTAMP,'YYYYMMDDHHMISSFF3') 20070309020738123 VARCHAR_FORMAT(TMSTAMP,'YYYYMMDDHH24MISS') 20070309140738 VARCHAR_FORMAT(TMSTAMP,'YYYYMMDDHHMI') 200703090207 VARCHAR_FORMAT(TMSTAMP,'DD/MM/YY') 09/03/07 VARCHAR_FORMAT(TMSTAMP,'MM-DD-YYYY') 03-09-2007 VARCHAR_FORMAT(TMSTAMP,'J') 2454169 VARCHAR_FORMAT(TMSTAMP,'Q') 1 VARCHAR_FORMAT(TMSTAMP,'W') 2 VARCHAR_FORMAT(TMSTAMP,'IW') 10 VARCHAR_FORMAT(TMSTAMP,'WW') 10 VARCHAR_FORMAT(TMSTAMP,'Month','en_US') March VARCHAR_FORMAT(TMSTAMP,'MONTH','en_US') MARCH VARCHAR_FORMAT(TMSTAMP,'MON','en_US') MAR VARCHAR_FORMAT(TMSTAMP,'Day','en_US') Friday VARCHAR_FORMAT(TMSTAMP,'DAY','en_US') FRIDAY VARCHAR_FORMAT(TMSTAMP,'Dy','en_US') Fri VARCHAR_FORMAT(TMSTAMP,'Month','de_DE') März VARCHAR_FORMAT(TMSTAMP,'MONTH','de_DE') MÄRZ VARCHAR_FORMAT(TMSTAMP,'MON','de_DE') MRZ VARCHAR_FORMAT(TMSTAMP,'Day','de_DE') Freitag VARCHAR_FORMAT(TMSTAMP,'DAY','de_DE') FREITAG VARCHAR_FORMAT(TMSTAMP,'Dy','de_DE') Fr
- Assume that 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 result data type in each case is VARCHAR(254).
Function invocation Result ------------------- ------ VARCHAR_FORMAT(DTE,'YYYYMMDD') 20070309 VARCHAR_FORMAT(DTE,'YYYYMMDDHH24MISS') 20070309000000
- Assume that the variables POSNUM and NEGNUM are defined as DECFLOAT(34) and have the following values: 1234.56 and -1234.56, respectively. The following examples show several invocations of the function and the resulting string values. The result data type in each case is 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'
