Function FORMAT
- Piotr Bojko
Function returns the formatted string by given format and arguments.
Function takes following parameters:
Type | Field |
|---|---|
string / varchar | Format to apply. |
any or array | Arguments to use with given format. Can be use as single value: AUX.FUNCTION('some-format', singlevalue)Or array like value AUX.FUNCTION('some-format', ARRAY[firstvalue, secondvalue,...])
|
Formatting
Here is a quick reference to all the conversion specifiers supported:
SPECIFIER | APPLIES TO | OUTPUT |
%a | floating point (except BigDecimal) | Hex output of floating point number |
%b | Any type | “true” if non-null, “false” if null |
%c | character | Unicode character |
%d | integer (incl. byte, short, int, long, bigint) | Decimal Integer |
%e | floating point | decimal number in scientific notation |
%f | floating point | decimal number |
%g | floating point | decimal number, possibly in scientific notation depending on the precision and value. |
%h | any type | Hex String of value from hashCode() method. |
%n | none | Platform-specific line separator. |
%o | integer (incl. byte, short, int, long, bigint) | Octal number |
%s | any type | String value |
%t | Date/Time (incl. long, Calendar, Date and TemporalAccessor) | %t is the prefix for Date/Time conversions. More formatting flags are needed after this. See Date/Time conversion below. |
%x | integer (incl. byte, short, int, long, bigint) | Hex string. |
Date and Time Formatting
FLAG | NOTES |
%tA | Full name of the day of the week, e.g. “ |
%ta | Abbreviated name of the week day e.g. “ |
%tB | Full name of the month e.g. “ |
%tb | Abbreviated month name e.g. “ |
%tC | Century part of year formatted with two digits e.g. “00” through “99”. |
%tc | Date and time formatted with “ |
%tD | Date formatted as “ |
%td | Day of the month formatted with two digits. e.g. “ |
%te | Day of the month formatted without a leading 0 e.g. “1” to “31”. |
%tF | ISO 8601 formatted date with “ |
%tH | Hour of the day for the 24-hour clock e.g. “ |
%th | Same as %tb. |
%tI | Hour of the day for the 12-hour clock e.g. “ |
%tj | Day of the year formatted with leading 0s e.g. “ |
%tk | Hour of the day for the 24 hour clock without a leading 0 e.g. “ |
%tl | Hour of the day for the 12-hour click without a leading 0 e.g. “ |
%tM | Minute within the hour formatted a leading 0 e.g. “ |
%tm | Month formatted with a leading 0 e.g. “ |
%tN | Nanosecond formatted with 9 digits and leading 0s e.g. “000000000” to “999999999”. |
%tp | Locale specific “am” or “pm” marker. |
%tQ | Milliseconds since epoch Jan 1 , 1970 00:00:00 UTC. |
%tR | Time formatted as 24-hours e.g. “ |
%tr | Time formatted as 12-hours e.g. “ |
%tS | Seconds within the minute formatted with 2 digits e.g. “00” to “60”. “60” is required to support leap seconds. |
%ts | Seconds since the epoch Jan 1, 1970 00:00:00 UTC. |
%tT | Time formatted as 24-hours e.g. “ |
%tY | Year formatted with 4 digits e.g. “ |
%ty | Year formatted with 2 digits e.g. “ |
%tZ | Time zone abbreviation. e.g. “ |
%tz | Time Zone Offset from GMT e.g. “
“. |
Argument Index
An argument index is specified as a number ending with a “$” after the “%” and selects the specified argument in the argument list.
AUX.FORMAT("%2$s", ARRAY[32, "Hello"]); // prints: "Hello"Formatting an Integer
With the %d format specifier, you can use an argument of all integral types including byte, short, int, long and BigInteger.
Default formatting:
Specifying a width:
Left-justifying within the specified width:
Pad with zeros:
Print positive numbers with a “+”:
(Negative numbers always have the “-” included):
A space before positive numbers.
A “-” is included for negative numbers as per normal.
Use locale-specific thousands separator:
For the US locale, it is “,”:
Enclose negative numbers within parentheses (“()”) and skip the "-":
Octal output:
Hex output:
Alternate representation for octal and hex output:
Prints octal numbers with a leading “0” and hex numbers with leading “0x“.
String and Character Conversion
Default formatting:
Prints the whole string.
Specify Field Length
Left Justify Text
Specify Maximum Number of Characters
Field Width and Maximum Number of Characters