org.h2.expression.function.ToCharFunction Maven / Gradle / Ivy
Show all versions of h2-mvstore Show documentation
/*
* Copyright 2004-2023 H2 Group. Multiple-Licensed under the MPL 2.0,
* and the EPL 1.0 (https://h2database.com/html/license.html).
* Initial Developer: Daniel Gredler
*/
package org.h2.expression.function;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DateFormatSymbols;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Currency;
import java.util.Locale;
import org.h2.api.ErrorCode;
import org.h2.engine.SessionLocal;
import org.h2.expression.Expression;
import org.h2.expression.TypedValueExpression;
import org.h2.message.DbException;
import org.h2.util.DateTimeUtils;
import org.h2.util.StringUtils;
import org.h2.util.TimeZoneProvider;
import org.h2.value.TypeInfo;
import org.h2.value.Value;
import org.h2.value.ValueTimeTimeZone;
import org.h2.value.ValueTimestamp;
import org.h2.value.ValueTimestampTimeZone;
import org.h2.value.ValueVarchar;
/**
* Emulates Oracle's TO_CHAR function.
*/
public final class ToCharFunction extends FunctionN {
/**
* The beginning of the Julian calendar.
*/
public static final int JULIAN_EPOCH = -2_440_588;
private static final int[] ROMAN_VALUES = { 1000, 900, 500, 400, 100, 90, 50, 40, 10, 9,
5, 4, 1 };
private static final String[] ROMAN_NUMERALS = { "M", "CM", "D", "CD", "C", "XC",
"L", "XL", "X", "IX", "V", "IV", "I" };
/**
* The month field.
*/
public static final int MONTHS = 0;
/**
* The month field (short form).
*/
public static final int SHORT_MONTHS = 1;
/**
* The weekday field.
*/
public static final int WEEKDAYS = 2;
/**
* The weekday field (short form).
*/
public static final int SHORT_WEEKDAYS = 3;
/**
* The AM / PM field.
*/
static final int AM_PM = 4;
private static volatile String[][] NAMES;
/**
* Emulates Oracle's TO_CHAR(number) function.
*
*
* TO_CHAR(number) function
* Input
* Output
* Closest {@link DecimalFormat} Equivalent ,
* Grouping separator.
* , .
* Decimal separator.
* . $
* Leading dollar sign.
* $ 0
* Leading or trailing zeroes.
* 0 9
* Digit.
* # B
* Blanks integer part of a fixed point number less than 1.
* # C
* ISO currency symbol.
* \u00A4 D
* Local decimal separator.
* . EEEE
* Returns a value in scientific notation.
* E FM
* Returns values with no leading or trailing spaces.
* None. G
* Local grouping separator.
* , L
* Local currency symbol.
* \u00A4 MI
* Negative values get trailing minus sign,
* positive get trailing space.
* - PR
* Negative values get enclosing angle brackets,
* positive get spaces.
* None. RN
* Returns values in Roman numerals.
* None. S
* Returns values with leading/trailing +/- signs.
* None. TM
* Returns smallest number of characters possible.
* None. U
* Returns the dual currency symbol.
* None. V
* Returns a value multiplied by 10^n.
* None. X
* Hex value.
* None.
* See also TO_CHAR(number) and number format models
* in the Oracle documentation.
*
* @param number the number to format
* @param format the format pattern to use (if any)
* @param nlsParam the NLS parameter (if any)
* @return the formatted number
*/
public static String toChar(BigDecimal number, String format,
@SuppressWarnings("unused") String nlsParam) {
// short-circuit logic for formats that don't follow common logic below
String formatUp = format != null ? StringUtils.toUpperEnglish(format) : null;
if (formatUp == null || formatUp.equals("TM") || formatUp.equals("TM9")) {
String s = number.toPlainString();
return s.startsWith("0.") ? s.substring(1) : s;
} else if (formatUp.equals("TME")) {
int pow = number.precision() - number.scale() - 1;
number = number.movePointLeft(pow);
return number.toPlainString() + "E" +
(pow < 0 ? '-' : '+') + (Math.abs(pow) < 10 ? "0" : "") + Math.abs(pow);
} else if (formatUp.equals("RN")) {
boolean lowercase = format.startsWith("r");
String rn = StringUtils.pad(toRomanNumeral(number.intValue()), 15, " ", false);
return lowercase ? rn.toLowerCase() : rn;
} else if (formatUp.equals("FMRN")) {
boolean lowercase = format.charAt(2) == 'r';
String rn = toRomanNumeral(number.intValue());
return lowercase ? rn.toLowerCase() : rn;
} else if (formatUp.endsWith("X")) {
return toHex(number, format);
}
String originalFormat = format;
DecimalFormatSymbols symbols = DecimalFormatSymbols.getInstance();
char localGrouping = symbols.getGroupingSeparator();
char localDecimal = symbols.getDecimalSeparator();
boolean leadingSign = formatUp.startsWith("S");
if (leadingSign) {
format = format.substring(1);
}
boolean trailingSign = formatUp.endsWith("S");
if (trailingSign) {
format = format.substring(0, format.length() - 1);
}
boolean trailingMinus = formatUp.endsWith("MI");
if (trailingMinus) {
format = format.substring(0, format.length() - 2);
}
boolean angleBrackets = formatUp.endsWith("PR");
if (angleBrackets) {
format = format.substring(0, format.length() - 2);
}
int v = formatUp.indexOf('V');
if (v >= 0) {
int digits = 0;
for (int i = v + 1; i < format.length(); i++) {
char c = format.charAt(i);
if (c == '0' || c == '9') {
digits++;
}
}
number = number.movePointRight(digits);
format = format.substring(0, v) + format.substring(v + 1);
}
Integer power;
if (format.endsWith("EEEE")) {
power = number.precision() - number.scale() - 1;
number = number.movePointLeft(power);
format = format.substring(0, format.length() - 4);
} else {
power = null;
}
int maxLength = 1;
boolean fillMode = !formatUp.startsWith("FM");
if (!fillMode) {
format = format.substring(2);
}
// blanks flag doesn't seem to actually do anything
format = format.replaceAll("[Bb]", "");
// if we need to round the number to fit into the format specified,
// go ahead and do that first
int separator = findDecimalSeparator(format);
int formatScale = calculateScale(format, separator);
int numberScale = number.scale();
if (formatScale < numberScale) {
number = number.setScale(formatScale, RoundingMode.HALF_UP);
} else if (numberScale < 0) {
number = number.setScale(0);
}
// any 9s to the left of the decimal separator but to the right of a
// 0 behave the same as a 0, e.g. "09999.99" -> "00000.99"
for (int i = format.indexOf('0'); i >= 0 && i < separator; i++) {
if (format.charAt(i) == '9') {
format = format.substring(0, i) + "0" + format.substring(i + 1);
}
}
StringBuilder output = new StringBuilder();
String unscaled = (number.abs().compareTo(BigDecimal.ONE) < 0 ?
zeroesAfterDecimalSeparator(number) : "") +
number.unscaledValue().abs().toString();
// start at the decimal point and fill in the numbers to the left,
// working our way from right to left
int i = separator - 1;
int j = unscaled.length() - number.scale() - 1;
for (; i >= 0; i--) {
char c = format.charAt(i);
maxLength++;
if (c == '9' || c == '0') {
if (j >= 0) {
char digit = unscaled.charAt(j);
output.insert(0, digit);
j--;
} else if (c == '0' && power == null) {
output.insert(0, '0');
}
} else if (c == ',') {
// only add the grouping separator if we have more numbers
if (j >= 0 || (i > 0 && format.charAt(i - 1) == '0')) {
output.insert(0, c);
}
} else if (c == 'G' || c == 'g') {
// only add the grouping separator if we have more numbers
if (j >= 0 || (i > 0 && format.charAt(i - 1) == '0')) {
output.insert(0, localGrouping);
}
} else if (c == 'C' || c == 'c') {
Currency currency = getCurrency();
output.insert(0, currency.getCurrencyCode());
maxLength += 6;
} else if (c == 'L' || c == 'l' || c == 'U' || c == 'u') {
Currency currency = getCurrency();
output.insert(0, currency.getSymbol());
maxLength += 9;
} else if (c == '$') {
Currency currency = getCurrency();
String cs = currency.getSymbol();
output.insert(0, cs);
} else {
throw DbException.get(
ErrorCode.INVALID_TO_CHAR_FORMAT, originalFormat);
}
}
// if the format (to the left of the decimal point) was too small
// to hold the number, return a big "######" string
if (j >= 0) {
return StringUtils.pad("", format.length() + 1, "#", true);
}
if (separator < format.length()) {
// add the decimal point
maxLength++;
char pt = format.charAt(separator);
if (pt == 'd' || pt == 'D') {
output.append(localDecimal);
} else {
output.append(pt);
}
// start at the decimal point and fill in the numbers to the right,
// working our way from left to right
i = separator + 1;
j = unscaled.length() - number.scale();
for (; i < format.length(); i++) {
char c = format.charAt(i);
maxLength++;
if (c == '9' || c == '0') {
if (j < unscaled.length()) {
char digit = unscaled.charAt(j);
output.append(digit);
j++;
} else {
if (c == '0' || fillMode) {
output.append('0');
}
}
} else {
throw DbException.get(
ErrorCode.INVALID_TO_CHAR_FORMAT, originalFormat);
}
}
}
addSign(output, number.signum(), leadingSign, trailingSign,
trailingMinus, angleBrackets, fillMode);
if (power != null) {
output.append('E');
output.append(power < 0 ? '-' : '+');
output.append(Math.abs(power) < 10 ? "0" : "");
output.append(Math.abs(power));
}
if (fillMode) {
if (power != null) {
output.insert(0, ' ');
} else {
while (output.length() < maxLength) {
output.insert(0, ' ');
}
}
}
return output.toString();
}
private static Currency getCurrency() {
Locale locale = Locale.getDefault();
return Currency.getInstance(locale.getCountry().length() == 2 ? locale : Locale.US);
}
private static String zeroesAfterDecimalSeparator(BigDecimal number) {
final String numberStr = number.toPlainString();
final int idx = numberStr.indexOf('.');
if (idx < 0) {
return "";
}
int i = idx + 1;
boolean allZeroes = true;
int length = numberStr.length();
for (; i < length; i++) {
if (numberStr.charAt(i) != '0') {
allZeroes = false;
break;
}
}
final char[] zeroes = new char[allZeroes ? length - idx - 1: i - 1 - idx];
Arrays.fill(zeroes, '0');
return String.valueOf(zeroes);
}
private static void addSign(StringBuilder output, int signum,
boolean leadingSign, boolean trailingSign, boolean trailingMinus,
boolean angleBrackets, boolean fillMode) {
if (angleBrackets) {
if (signum < 0) {
output.insert(0, '<');
output.append('>');
} else if (fillMode) {
output.insert(0, ' ');
output.append(' ');
}
} else {
String sign;
if (signum == 0) {
sign = "";
} else if (signum < 0) {
sign = "-";
} else {
if (leadingSign || trailingSign) {
sign = "+";
} else if (fillMode) {
sign = " ";
} else {
sign = "";
}
}
if (trailingMinus || trailingSign) {
output.append(sign);
} else {
output.insert(0, sign);
}
}
}
private static int findDecimalSeparator(String format) {
int index = format.indexOf('.');
if (index == -1) {
index = format.indexOf('D');
if (index == -1) {
index = format.indexOf('d');
if (index == -1) {
index = format.length();
}
}
}
return index;
}
private static int calculateScale(String format, int separator) {
int scale = 0;
for (int i = separator; i < format.length(); i++) {
char c = format.charAt(i);
if (c == '0' || c == '9') {
scale++;
}
}
return scale;
}
private static String toRomanNumeral(int number) {
StringBuilder result = new StringBuilder();
for (int i = 0; i < ROMAN_VALUES.length; i++) {
int value = ROMAN_VALUES[i];
String numeral = ROMAN_NUMERALS[i];
while (number >= value) {
result.append(numeral);
number -= value;
}
}
return result.toString();
}
private static String toHex(BigDecimal number, String format) {
boolean fillMode = !StringUtils.toUpperEnglish(format).startsWith("FM");
boolean uppercase = !format.contains("x");
boolean zeroPadded = format.startsWith("0");
int digits = 0;
for (int i = 0; i < format.length(); i++) {
char c = format.charAt(i);
if (c == '0' || c == 'X' || c == 'x') {
digits++;
}
}
int i = number.setScale(0, RoundingMode.HALF_UP).intValue();
String hex = Integer.toHexString(i);
if (digits < hex.length()) {
hex = StringUtils.pad("", digits + 1, "#", true);
} else {
if (uppercase) {
hex = StringUtils.toUpperEnglish(hex);
}
if (zeroPadded) {
hex = StringUtils.pad(hex, digits, "0", false);
}
if (fillMode) {
hex = StringUtils.pad(hex, format.length() + 1, " ", false);
}
}
return hex;
}
/**
* Get the date (month / weekday / ...) names.
*
* @param names the field
* @return the names
*/
public static String[] getDateNames(int names) {
String[][] result = NAMES;
if (result == null) {
result = new String[5][];
DateFormatSymbols dfs = DateFormatSymbols.getInstance();
result[MONTHS] = dfs.getMonths();
String[] months = dfs.getShortMonths();
for (int i = 0; i < 12; i++) {
String month = months[i];
if (month.endsWith(".")) {
months[i] = month.substring(0, month.length() - 1);
}
}
result[SHORT_MONTHS] = months;
result[WEEKDAYS] = dfs.getWeekdays();
result[SHORT_WEEKDAYS] = dfs.getShortWeekdays();
result[AM_PM] = dfs.getAmPmStrings();
NAMES = result;
}
return result[names];
}
/**
* Used for testing.
*/
public static void clearNames() {
NAMES = null;
}
/**
* Returns time zone display name or ID for the specified date-time value.
*
* @param session
* the session
* @param value
* value
* @param tzd
* if {@code true} return TZD (time zone region with Daylight Saving
* Time information included), if {@code false} return TZR (time zone
* region)
* @return time zone display name or ID
*/
private static String getTimeZone(SessionLocal session, Value value, boolean tzd) {
if (value instanceof ValueTimestampTimeZone) {
return DateTimeUtils.timeZoneNameFromOffsetSeconds(((ValueTimestampTimeZone) value)
.getTimeZoneOffsetSeconds());
} else if (value instanceof ValueTimeTimeZone) {
return DateTimeUtils.timeZoneNameFromOffsetSeconds(((ValueTimeTimeZone) value)
.getTimeZoneOffsetSeconds());
} else {
TimeZoneProvider tz = session.currentTimeZone();
if (tzd) {
ValueTimestamp v = (ValueTimestamp) value.convertTo(TypeInfo.TYPE_TIMESTAMP, session);
return tz.getShortId(tz.getEpochSecondsFromLocal(v.getDateValue(), v.getTimeNanos()));
}
return tz.getId();
}
}
/**
* Emulates Oracle's TO_CHAR(datetime) function.
*
*
* TO_CHAR(datetime) function
* Input
* Output
* Closest {@link SimpleDateFormat} Equivalent - / , . ; : "text"
* Reproduced verbatim.
* 'text' A.D. AD B.C. BC
* Era designator, with or without periods.
* G A.M. AM P.M. PM
* AM/PM marker.
* a CC SCC
* Century.
* None. D
* Day of week.
* u DAY
* Name of day.
* EEEE DY
* Abbreviated day name.
* EEE DD
* Day of month.
* d DDD
* Day of year.
* D DL
* Long date format.
* EEEE, MMMM d, yyyy DS
* Short date format.
* MM/dd/yyyy E
* Abbreviated era name (Japanese, Chinese, Thai)
* None. EE
* Full era name (Japanese, Chinese, Thai)
* None. FF[1-9]
* Fractional seconds.
* S FM
* Returns values with no leading or trailing spaces.
* None. FX
* Requires exact matches between character data and format model.
* None. HH HH12
* Hour in AM/PM (1-12).
* hh HH24
* Hour in day (0-23).
* HH IW
* Week in year.
* w WW
* Week in year.
* w W
* Week in month.
* W IYYY IYY IY I
* Last 4/3/2/1 digit(s) of ISO year.
* yyyy yyy yy y RRRR RR
* Last 4/2 digits of year.
* yyyy yy Y,YYY
* Year with comma.
* None. YEAR SYEAR
* Year spelled out (S prefixes BC years with minus sign).
* None. YYYY SYYYY
* 4-digit year (S prefixes BC years with minus sign).
* yyyy YYY YY Y
* Last 3/2/1 digit(s) of year.
* yyy yy y J
* Julian day (number of days since January 1, 4712 BC).
* None. MI
* Minute in hour.
* mm MM
* Month in year.
* MM MON
* Abbreviated name of month.
* MMM MONTH
* Name of month, padded with spaces.
* MMMM RM
* Roman numeral month.
* None. Q
* Quarter of year.
* None. SS
* Seconds in minute.
* ss SSSSS
* Seconds in day.
* None. TS
* Short time format.
* h:mm:ss aa TZD
* Daylight savings time zone abbreviation.
* z TZR
* Time zone region information.
* zzzz X
* Local radix character.
* None.
*
* See also TO_CHAR(datetime) and datetime format models
* in the Oracle documentation.
*
* @param session the session
* @param value the date-time value to format
* @param format the format pattern to use (if any)
* @param nlsParam the NLS parameter (if any)
*
* @return the formatted timestamp
*/
public static String toCharDateTime(SessionLocal session, Value value, String format,
@SuppressWarnings("unused") String nlsParam) {
long[] a = DateTimeUtils.dateAndTimeFromValue(value, session);
long dateValue = a[0];
long timeNanos = a[1];
int year = DateTimeUtils.yearFromDateValue(dateValue);
int monthOfYear = DateTimeUtils.monthFromDateValue(dateValue);
int dayOfMonth = DateTimeUtils.dayFromDateValue(dateValue);
int posYear = Math.abs(year);
int second = (int) (timeNanos / 1_000_000_000);
int nanos = (int) (timeNanos - second * 1_000_000_000);
int minute = second / 60;
second -= minute * 60;
int hour = minute / 60;
minute -= hour * 60;
int h12 = (hour + 11) % 12 + 1;
boolean isAM = hour < 12;
if (format == null) {
format = "DD-MON-YY HH.MI.SS.FF PM";
}
StringBuilder output = new StringBuilder();
boolean fillMode = true;
for (int i = 0, length = format.length(); i < length;) {
Capitalization cap;
// AD / BC
if ((cap = containsAt(format, i, "A.D.", "B.C.")) != null) {
String era = year > 0 ? "A.D." : "B.C.";
output.append(cap.apply(era));
i += 4;
} else if ((cap = containsAt(format, i, "AD", "BC")) != null) {
String era = year > 0 ? "AD" : "BC";
output.append(cap.apply(era));
i += 2;
// AM / PM
} else if ((cap = containsAt(format, i, "A.M.", "P.M.")) != null) {
String am = isAM ? "A.M." : "P.M.";
output.append(cap.apply(am));
i += 4;
} else if ((cap = containsAt(format, i, "AM", "PM")) != null) {
String am = isAM ? "AM" : "PM";
output.append(cap.apply(am));
i += 2;
// Long/short date/time format
} else if (containsAt(format, i, "DL") != null) {
String day = getDateNames(WEEKDAYS)[DateTimeUtils.getSundayDayOfWeek(dateValue)];
String month = getDateNames(MONTHS)[monthOfYear - 1];
output.append(day).append(", ").append(month).append(' ').append(dayOfMonth).append(", ");
StringUtils.appendZeroPadded(output, 4, posYear);
i += 2;
} else if (containsAt(format, i, "DS") != null) {
StringUtils.appendTwoDigits(output, monthOfYear).append('/');
StringUtils.appendTwoDigits(output, dayOfMonth).append('/');
StringUtils.appendZeroPadded(output, 4, posYear);
i += 2;
} else if (containsAt(format, i, "TS") != null) {
output.append(h12).append(':');
StringUtils.appendTwoDigits(output, minute).append(':');
StringUtils.appendTwoDigits(output, second).append(' ').append(getDateNames(AM_PM)[isAM ? 0 : 1]);
i += 2;
// Day
} else if (containsAt(format, i, "DDD") != null) {
output.append(DateTimeUtils.getDayOfYear(dateValue));
i += 3;
} else if (containsAt(format, i, "DD") != null) {
StringUtils.appendTwoDigits(output, dayOfMonth);
i += 2;
} else if ((cap = containsAt(format, i, "DY")) != null) {
String day = getDateNames(SHORT_WEEKDAYS)[DateTimeUtils.getSundayDayOfWeek(dateValue)];
output.append(cap.apply(day));
i += 2;
} else if ((cap = containsAt(format, i, "DAY")) != null) {
String day = getDateNames(WEEKDAYS)[DateTimeUtils.getSundayDayOfWeek(dateValue)];
if (fillMode) {
day = StringUtils.pad(day, "Wednesday".length(), " ", true);
}
output.append(cap.apply(day));
i += 3;
} else if (containsAt(format, i, "D") != null) {
output.append(DateTimeUtils.getSundayDayOfWeek(dateValue));
i += 1;
} else if (containsAt(format, i, "J") != null) {
output.append(DateTimeUtils.absoluteDayFromDateValue(dateValue) - JULIAN_EPOCH);
i += 1;
// Hours
} else if (containsAt(format, i, "HH24") != null) {
StringUtils.appendTwoDigits(output, hour);
i += 4;
} else if (containsAt(format, i, "HH12") != null) {
StringUtils.appendTwoDigits(output, h12);
i += 4;
} else if (containsAt(format, i, "HH") != null) {
StringUtils.appendTwoDigits(output, h12);
i += 2;
// Minutes
} else if (containsAt(format, i, "MI") != null) {
StringUtils.appendTwoDigits(output, minute);
i += 2;
// Seconds
} else if (containsAt(format, i, "SSSSS") != null) {
int seconds = (int) (timeNanos / 1_000_000_000);
output.append(seconds);
i += 5;
} else if (containsAt(format, i, "SS") != null) {
StringUtils.appendTwoDigits(output, second);
i += 2;
// Fractional seconds
} else if (containsAt(format, i, "FF1", "FF2",
"FF3", "FF4", "FF5", "FF6", "FF7", "FF8", "FF9") != null) {
int x = format.charAt(i + 2) - '0';
int ff = (int) (nanos * Math.pow(10, x - 9));
StringUtils.appendZeroPadded(output, x, ff);
i += 3;
} else if (containsAt(format, i, "FF") != null) {
StringUtils.appendZeroPadded(output, 9, nanos);
i += 2;
// Time zone
} else if (containsAt(format, i, "TZR") != null) {
output.append(getTimeZone(session, value, false));
i += 3;
} else if (containsAt(format, i, "TZD") != null) {
output.append(getTimeZone(session, value, true));
i += 3;
} else if (containsAt(format, i, "TZH") != null) {
int hours = DateTimeFunction.extractDateTime(session, value, DateTimeFunction.TIMEZONE_HOUR);
output.append( hours < 0 ? '-' : '+');
StringUtils.appendTwoDigits(output, Math.abs(hours));
i += 3;
} else if (containsAt(format, i, "TZM") != null) {
StringUtils.appendTwoDigits(output,
Math.abs(DateTimeFunction.extractDateTime(session, value, DateTimeFunction.TIMEZONE_MINUTE)));
i += 3;
// Week
} else if (containsAt(format, i, "WW") != null) {
StringUtils.appendTwoDigits(output, (DateTimeUtils.getDayOfYear(dateValue) - 1) / 7 + 1);
i += 2;
} else if (containsAt(format, i, "IW") != null) {
StringUtils.appendTwoDigits(output, DateTimeUtils.getIsoWeekOfYear(dateValue));
i += 2;
} else if (containsAt(format, i, "W") != null) {
output.append((dayOfMonth - 1) / 7 + 1);
i += 1;
// Year
} else if (containsAt(format, i, "Y,YYY") != null) {
output.append(new DecimalFormat("#,###").format(posYear));
i += 5;
} else if (containsAt(format, i, "SYYYY") != null) {
// Should be <= 0, but Oracle prints negative years with off-by-one difference
if (year < 0) {
output.append('-');
}
StringUtils.appendZeroPadded(output, 4, posYear);
i += 5;
} else if (containsAt(format, i, "YYYY", "RRRR") != null) {
StringUtils.appendZeroPadded(output, 4, posYear);
i += 4;
} else if (containsAt(format, i, "IYYY") != null) {
StringUtils.appendZeroPadded(output, 4, Math.abs(DateTimeUtils.getIsoWeekYear(dateValue)));
i += 4;
} else if (containsAt(format, i, "YYY") != null) {
StringUtils.appendZeroPadded(output, 3, posYear % 1000);
i += 3;
} else if (containsAt(format, i, "IYY") != null) {
StringUtils.appendZeroPadded(output, 3, Math.abs(DateTimeUtils.getIsoWeekYear(dateValue)) % 1000);
i += 3;
} else if (containsAt(format, i, "YY", "RR") != null) {
StringUtils.appendTwoDigits(output, posYear % 100);
i += 2;
} else if (containsAt(format, i, "IY") != null) {
StringUtils.appendTwoDigits(output, Math.abs(DateTimeUtils.getIsoWeekYear(dateValue)) % 100);
i += 2;
} else if (containsAt(format, i, "Y") != null) {
output.append(posYear % 10);
i += 1;
} else if (containsAt(format, i, "I") != null) {
output.append(Math.abs(DateTimeUtils.getIsoWeekYear(dateValue)) % 10);
i += 1;
// Month / quarter
} else if ((cap = containsAt(format, i, "MONTH")) != null) {
String month = getDateNames(MONTHS)[monthOfYear - 1];
if (fillMode) {
month = StringUtils.pad(month, "September".length(), " ", true);
}
output.append(cap.apply(month));
i += 5;
} else if ((cap = containsAt(format, i, "MON")) != null) {
String month = getDateNames(SHORT_MONTHS)[monthOfYear - 1];
output.append(cap.apply(month));
i += 3;
} else if (containsAt(format, i, "MM") != null) {
StringUtils.appendTwoDigits(output, monthOfYear);
i += 2;
} else if ((cap = containsAt(format, i, "RM")) != null) {
output.append(cap.apply(toRomanNumeral(monthOfYear)));
i += 2;
} else if (containsAt(format, i, "Q") != null) {
int q = 1 + ((monthOfYear - 1) / 3);
output.append(q);
i += 1;
// Local radix character
} else if (containsAt(format, i, "X") != null) {
char c = DecimalFormatSymbols.getInstance().getDecimalSeparator();
output.append(c);
i += 1;
// Format modifiers
} else if (containsAt(format, i, "FM") != null) {
fillMode = !fillMode;
i += 2;
} else if (containsAt(format, i, "FX") != null) {
i += 2;
// Literal text
} else if (containsAt(format, i, "\"") != null) {
for (i = i + 1; i < format.length(); i++) {
char c = format.charAt(i);
if (c != '"') {
output.append(c);
} else {
i++;
break;
}
}
} else if (format.charAt(i) == '-'
|| format.charAt(i) == '/'
|| format.charAt(i) == ','
|| format.charAt(i) == '.'
|| format.charAt(i) == ';'
|| format.charAt(i) == ':'
|| format.charAt(i) == ' ') {
output.append(format.charAt(i));
i += 1;
// Anything else
} else {
throw DbException.get(ErrorCode.INVALID_TO_CHAR_FORMAT, format);
}
}
return output.toString();
}
/**
* Returns a capitalization strategy if the specified string contains any of
* the specified substrings at the specified index. The capitalization
* strategy indicates the casing of the substring that was found. If none of
* the specified substrings are found, this method returns null
* .
*
* @param s the string to check
* @param index the index to check at
* @param substrings the substrings to check for within the string
* @return a capitalization strategy if the specified string contains any of
* the specified substrings at the specified index,
* null otherwise
*/
private static Capitalization containsAt(String s, int index,
String... substrings) {
for (String substring : substrings) {
if (index + substring.length() <= s.length()) {
boolean found = true;
Boolean up1 = null;
Boolean up2 = null;
for (int i = 0; i < substring.length(); i++) {
char c1 = s.charAt(index + i);
char c2 = substring.charAt(i);
if (c1 != c2 && Character.toUpperCase(c1) != Character.toUpperCase(c2)) {
found = false;
break;
} else if (Character.isLetter(c1)) {
if (up1 == null) {
up1 = Character.isUpperCase(c1);
} else if (up2 == null) {
up2 = Character.isUpperCase(c1);
}
}
}
if (found) {
return Capitalization.toCapitalization(up1, up2);
}
}
}
return null;
}
/** Represents a capitalization / casing strategy. */
public enum Capitalization {
/**
* All letters are uppercased.
*/
UPPERCASE,
/**
* All letters are lowercased.
*/
LOWERCASE,
/**
* The string is capitalized (first letter uppercased, subsequent
* letters lowercased).
*/
CAPITALIZE;
/**
* Returns the capitalization / casing strategy which should be used
* when the first and second letters have the specified casing.
*
* @param up1 whether or not the first letter is uppercased
* @param up2 whether or not the second letter is uppercased
* @return the capitalization / casing strategy which should be used
* when the first and second letters have the specified casing
*/
static Capitalization toCapitalization(Boolean up1, Boolean up2) {
if (up1 == null) {
return Capitalization.CAPITALIZE;
} else if (up2 == null) {
return up1 ? Capitalization.UPPERCASE : Capitalization.LOWERCASE;
} else if (up1) {
return up2 ? Capitalization.UPPERCASE : Capitalization.CAPITALIZE;
} else {
return Capitalization.LOWERCASE;
}
}
/**
* Applies this capitalization strategy to the specified string.
*
* @param s the string to apply this strategy to
* @return the resultant string
*/
public String apply(String s) {
if (s == null || s.isEmpty()) {
return s;
}
switch (this) {
case UPPERCASE:
return StringUtils.toUpperEnglish(s);
case LOWERCASE:
return StringUtils.toLowerEnglish(s);
case CAPITALIZE:
return Character.toUpperCase(s.charAt(0)) +
(s.length() > 1 ? StringUtils.toLowerEnglish(s).substring(1) : "");
default:
throw new IllegalArgumentException(
"Unknown capitalization strategy: " + this);
}
}
}
public ToCharFunction(Expression arg1, Expression arg2, Expression arg3) {
super(arg2 == null ? new Expression[] { arg1 }
: arg3 == null ? new Expression[] { arg1, arg2 } : new Expression[] { arg1, arg2, arg3 });
}
@Override
public Value getValue(SessionLocal session, Value v1, Value v2, Value v3) {
switch (v1.getValueType()) {
case Value.DATE:
case Value.TIME:
case Value.TIME_TZ:
case Value.TIMESTAMP:
case Value.TIMESTAMP_TZ:
v1 = ValueVarchar.get(toCharDateTime(session, v1, v2 == null ? null : v2.getString(),
v3 == null ? null : v3.getString()), session);
break;
case Value.TINYINT:
case Value.SMALLINT:
case Value.INTEGER:
case Value.BIGINT:
case Value.NUMERIC:
case Value.REAL:
case Value.DOUBLE:
case Value.DECFLOAT:
v1 = ValueVarchar.get(toChar(v1.getBigDecimal(), v2 == null ? null : v2.getString(),
v3 == null ? null : v3.getString()), session);
break;
default:
v1 = ValueVarchar.get(v1.getString(), session);
}
return v1;
}
@Override
public Expression optimize(SessionLocal session) {
boolean allConst = optimizeArguments(session, true);
type = TypeInfo.TYPE_VARCHAR;
if (allConst) {
return TypedValueExpression.getTypedIfNull(getValue(session), type);
}
return this;
}
@Override
public String getName() {
return "TO_CHAR";
}
}