This is an automated email from the ASF dual-hosted git repository.
maxgekk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new a6576de [SPARK-34755][SQL] Support the utils for transform number
format
a6576de is described below
commit a6576de9719204f6a87d2fc5e2e344bd1d0017a3
Author: Jiaan Geng <[email protected]>
AuthorDate: Wed Dec 29 11:07:06 2021 +0300
[SPARK-34755][SQL] Support the utils for transform number format
### What changes were proposed in this pull request?
Data Type Formatting Functions: `to_number` and `to_char` is very useful.
The implement has many different between `Postgresql` ,`Oracle` and
`Phoenix`.
So, this PR follows the implement of `to_number` in `Oracle` that give a
strict parameter verification.
So, this PR follows the implement of `to_number` in `Phoenix` that uses
BigDecimal.
This PR support the patterns for numeric formatting as follows:
Pattern | Description
-- | --
9 | Value with the specified number of digits
0 | Value with leading zeros
. (period) | Decimal point
, (comma) | Group (thousand) separator
S | Sign anchored to number (uses locale)
$ | a value with a leading dollar sign
D | Decimal point (uses locale)
G | Group separator (uses locale)
There are some mainstream database support the syntax.
**PostgreSQL:**
https://www.postgresql.org/docs/12/functions-formatting.html
**Oracle:**
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_NUMBER.html#GUID-D4807212-AFD7-48A7-9AED-BEC3E8809866
**Vertica**
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TO_NUMBER.htm?tocpath=SQL%20Reference%20Manual%7CSQL%20Functions%7CFormatting%20Functions%7C_____7
**Redshift**
https://docs.aws.amazon.com/redshift/latest/dg/r_TO_NUMBER.html
**DB2**
https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1544.htm
**Teradata**
https://docs.teradata.com/r/kmuOwjp1zEYg98JsB8fu_A/TH2cDXBn6tala29S536nqg
**Snowflake:**
https://docs.snowflake.net/manuals/sql-reference/functions/to_decimal.html
**Exasol**
https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/to_number.htm#TO_NUMBER
**Phoenix**
http://phoenix.incubator.apache.org/language/functions.html#to_number
**Singlestore**
https://docs.singlestore.com/v7.3/reference/sql-reference/numeric-functions/to-number/
**Intersystems**
https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_TONUMBER
Note: Based on discussion offline with cloud-fan ten months ago, this PR
only implement the utils for transform number format. Because the utils should
be review better.
### Why are the changes needed?
`to_number` and `to_char` are very useful for formatted currency to number
conversion.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Jenkins test
Closes #31847 from beliefer/SPARK-34755.
Lead-authored-by: Jiaan Geng <[email protected]>
Co-authored-by: gengjiaan <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
.../spark/sql/catalyst/util/NumberUtils.scala | 189 ++++++++++++
.../spark/sql/errors/QueryCompilationErrors.scala | 8 +
.../spark/sql/errors/QueryExecutionErrors.scala | 6 +
.../spark/sql/catalyst/util/NumberUtilsSuite.scala | 317 +++++++++++++++++++++
4 files changed, 520 insertions(+)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberUtils.scala
new file mode 100644
index 0000000..6efde2a
--- /dev/null
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberUtils.scala
@@ -0,0 +1,189 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql.catalyst.util
+
+import java.math.BigDecimal
+import java.text.{DecimalFormat, NumberFormat, ParsePosition}
+import java.util.Locale
+
+import org.apache.spark.sql.errors.{QueryCompilationErrors,
QueryExecutionErrors}
+import org.apache.spark.sql.types.Decimal
+import org.apache.spark.unsafe.types.UTF8String
+
+object NumberUtils {
+
+ private val pointSign = '.'
+ private val letterPointSign = 'D'
+ private val commaSign = ','
+ private val letterCommaSign = 'G'
+ private val minusSign = '-'
+ private val letterMinusSign = 'S'
+ private val dollarSign = '$'
+
+ private val commaSignStr = commaSign.toString
+
+ private def normalize(format: String): String = {
+ var notFindDecimalPoint = true
+ val normalizedFormat = format.toUpperCase(Locale.ROOT).map {
+ case '9' if notFindDecimalPoint => '#'
+ case '9' if !notFindDecimalPoint => '0'
+ case `letterPointSign` =>
+ notFindDecimalPoint = false
+ pointSign
+ case `letterCommaSign` => commaSign
+ case `letterMinusSign` => minusSign
+ case `pointSign` =>
+ notFindDecimalPoint = false
+ pointSign
+ case other => other
+ }
+ // If the comma is at the beginning or end of number format, then
DecimalFormat will be invalid.
+ // For example, "##,###," or ",###,###" for DecimalFormat is invalid, so
we must use "##,###"
+ // or "###,###".
+ normalizedFormat.stripPrefix(commaSignStr).stripSuffix(commaSignStr)
+ }
+
+ private def isSign(c: Char): Boolean = {
+ Set(pointSign, commaSign, minusSign, dollarSign).contains(c)
+ }
+
+ private def transform(format: String): String = {
+ if (format.contains(minusSign)) {
+ val positiveFormatString = format.replaceAll("-", "")
+ s"$positiveFormatString;$format"
+ } else {
+ format
+ }
+ }
+
+ private def check(normalizedFormat: String, numberFormat: String) = {
+ def invalidSignPosition(format: String, c: Char): Boolean = {
+ val signIndex = format.indexOf(c)
+ signIndex > 0 && signIndex < format.length - 1
+ }
+
+ if (normalizedFormat.count(_ == pointSign) > 1) {
+ throw QueryCompilationErrors.multipleSignInNumberFormatError(
+ s"'$letterPointSign' or '$pointSign'", numberFormat)
+ } else if (normalizedFormat.count(_ == minusSign) > 1) {
+ throw QueryCompilationErrors.multipleSignInNumberFormatError(
+ s"'$letterMinusSign' or '$minusSign'", numberFormat)
+ } else if (normalizedFormat.count(_ == dollarSign) > 1) {
+ throw
QueryCompilationErrors.multipleSignInNumberFormatError(s"'$dollarSign'",
numberFormat)
+ } else if (invalidSignPosition(normalizedFormat, minusSign)) {
+ throw QueryCompilationErrors.nonFistOrLastCharInNumberFormatError(
+ s"'$letterMinusSign' or '$minusSign'", numberFormat)
+ } else if (invalidSignPosition(normalizedFormat, dollarSign)) {
+ throw QueryCompilationErrors.nonFistOrLastCharInNumberFormatError(
+ s"'$dollarSign'", numberFormat)
+ }
+ }
+
+ /**
+ * Convert string to numeric based on the given number format.
+ * The format can consist of the following characters:
+ * '9': digit position (can be dropped if insignificant)
+ * '0': digit position (will not be dropped, even if insignificant)
+ * '.': decimal point (only allowed once)
+ * ',': group (thousands) separator
+ * 'S': sign anchored to number (uses locale)
+ * 'D': decimal point (uses locale)
+ * 'G': group separator (uses locale)
+ * '$': specifies that the input value has a leading $ (Dollar) sign.
+ *
+ * @param input the string need to converted
+ * @param numberFormat the given number format
+ * @return decimal obtained from string parsing
+ */
+ def parse(input: UTF8String, numberFormat: String): Decimal = {
+ val normalizedFormat = normalize(numberFormat)
+ check(normalizedFormat, numberFormat)
+
+ val precision = normalizedFormat.filterNot(isSign).length
+ val formatSplits = normalizedFormat.split(pointSign)
+ val scale = if (formatSplits.length == 1) {
+ 0
+ } else {
+ formatSplits(1).filterNot(isSign).length
+ }
+ val transformedFormat = transform(normalizedFormat)
+ val numberFormatInstance = NumberFormat.getInstance()
+ val numberDecimalFormat = numberFormatInstance.asInstanceOf[DecimalFormat]
+ numberDecimalFormat.setParseBigDecimal(true)
+ numberDecimalFormat.applyPattern(transformedFormat)
+ val inputStr = input.toString.trim
+ val inputSplits = inputStr.split(pointSign)
+ if (inputSplits.length == 1) {
+ if (inputStr.filterNot(isSign).length > precision - scale) {
+ throw QueryExecutionErrors.invalidNumberFormatError(numberFormat)
+ }
+ } else if (inputSplits(0).filterNot(isSign).length > precision - scale ||
+ inputSplits(1).filterNot(isSign).length > scale) {
+ throw QueryExecutionErrors.invalidNumberFormatError(numberFormat)
+ }
+ val number = numberDecimalFormat.parse(inputStr, new ParsePosition(0))
+ Decimal(number.asInstanceOf[BigDecimal])
+ }
+
+ /**
+ * Convert numeric to string based on the given number format.
+ * The format can consist of the following characters:
+ * '9': digit position (can be dropped if insignificant)
+ * '0': digit position (will not be dropped, even if insignificant)
+ * '.': decimal point (only allowed once)
+ * ',': group (thousands) separator
+ * 'S': sign anchored to number (uses locale)
+ * 'D': decimal point (uses locale)
+ * 'G': group separator (uses locale)
+ * '$': specifies that the input value has a leading $ (Dollar) sign.
+ *
+ * @param input the decimal to format
+ * @param numberFormat the format string
+ * @return The string after formatting input decimal
+ */
+ def format(input: Decimal, numberFormat: String): String = {
+ val normalizedFormat = normalize(numberFormat)
+ check(normalizedFormat, numberFormat)
+
+ val transformedFormat = transform(normalizedFormat)
+ val bigDecimal = input.toJavaBigDecimal
+ val decimalPlainStr = bigDecimal.toPlainString
+ if (decimalPlainStr.length > transformedFormat.length) {
+ transformedFormat.replaceAll("0", "#")
+ } else {
+ val decimalFormat = new DecimalFormat(transformedFormat)
+ var resultStr = decimalFormat.format(bigDecimal)
+ // Since we trimmed the comma at the beginning or end of number format
in function
+ // `normalize`, we restore the comma to the result here.
+ // For example, if the specified number format is "99,999," or
",999,999", function
+ // `normalize` normalize them to "##,###" or "###,###".
+ // new DecimalFormat("##,###").parse(12454) and new
DecimalFormat("###,###").parse(124546)
+ // will return "12,454" and "124,546" respectively. So we add ',' at the
end and head of
+ // the result, then the final output are "12,454," or ",124,546".
+ if (numberFormat.last == commaSign || numberFormat.last ==
letterCommaSign) {
+ resultStr = resultStr + commaSign
+ }
+ if (numberFormat.charAt(0) == commaSign || numberFormat.charAt(0) ==
letterCommaSign) {
+ resultStr = commaSign + resultStr
+ }
+
+ resultStr
+ }
+ }
+
+}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
index 79de8b6..300ba03 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
@@ -2365,4 +2365,12 @@ object QueryCompilationErrors {
def tableNotSupportTimeTravelError(tableName: Identifier):
UnsupportedOperationException = {
new UnsupportedOperationException(s"Table $tableName does not support time
travel.")
}
+
+ def multipleSignInNumberFormatError(message: String, numberFormat: String):
Throwable = {
+ new AnalysisException(s"Multiple $message in '$numberFormat'")
+ }
+
+ def nonFistOrLastCharInNumberFormatError(message: String, numberFormat:
String): Throwable = {
+ new AnalysisException(s"$message must be the first or last char in
'$numberFormat'")
+ }
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
index 6f0ed23..eb6e814 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
@@ -1920,4 +1920,10 @@ object QueryExecutionErrors {
s". To solve this try to set $maxDynamicPartitionsKey" +
s" to at least $numWrittenParts.")
}
+
+ def invalidNumberFormatError(format: String): Throwable = {
+ new IllegalArgumentException(
+ s"Format '$format' used for parsing string to number or " +
+ "formatting number to string is invalid")
+ }
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/NumberUtilsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/NumberUtilsSuite.scala
new file mode 100644
index 0000000..66a17dc
--- /dev/null
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/NumberUtilsSuite.scala
@@ -0,0 +1,317 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql.catalyst.util
+
+import org.apache.spark.SparkFunSuite
+import org.apache.spark.sql.AnalysisException
+import org.apache.spark.sql.catalyst.util.NumberUtils.{format, parse}
+import org.apache.spark.sql.types.Decimal
+import org.apache.spark.unsafe.types.UTF8String
+
+class NumberUtilsSuite extends SparkFunSuite {
+
+ private def failParseWithInvalidInput(
+ input: UTF8String, numberFormat: String, errorMsg: String): Unit = {
+ val e = intercept[IllegalArgumentException](parse(input, numberFormat))
+ assert(e.getMessage.contains(errorMsg))
+ }
+
+ private def failParseWithAnalysisException(
+ input: UTF8String, numberFormat: String, errorMsg: String): Unit = {
+ val e = intercept[AnalysisException](parse(input, numberFormat))
+ assert(e.getMessage.contains(errorMsg))
+ }
+
+ private def failFormatWithAnalysisException(
+ input: Decimal, numberFormat: String, errorMsg: String): Unit = {
+ val e = intercept[AnalysisException](format(input, numberFormat))
+ assert(e.getMessage.contains(errorMsg))
+ }
+
+ test("parse") {
+ failParseWithInvalidInput(UTF8String.fromString("454"), "",
+ "Format '' used for parsing string to number or formatting number to
string is invalid")
+
+ // Test '9' and '0'
+ failParseWithInvalidInput(UTF8String.fromString("454"), "9",
+ "Format '9' used for parsing string to number or formatting number to
string is invalid")
+ failParseWithInvalidInput(UTF8String.fromString("454"), "99",
+ "Format '99' used for parsing string to number or formatting number to
string is invalid")
+
+ Seq(
+ ("454", "999") -> Decimal(454),
+ ("054", "999") -> Decimal(54),
+ ("404", "999") -> Decimal(404),
+ ("450", "999") -> Decimal(450),
+ ("454", "9999") -> Decimal(454),
+ ("054", "9999") -> Decimal(54),
+ ("404", "9999") -> Decimal(404),
+ ("450", "9999") -> Decimal(450)
+ ).foreach { case ((str, format), expected) =>
+ assert(parse(UTF8String.fromString(str), format) === expected)
+ }
+
+ failParseWithInvalidInput(UTF8String.fromString("454"), "0",
+ "Format '0' used for parsing string to number or formatting number to
string is invalid")
+ failParseWithInvalidInput(UTF8String.fromString("454"), "00",
+ "Format '00' used for parsing string to number or formatting number to
string is invalid")
+
+ Seq(
+ ("454", "000") -> Decimal(454),
+ ("054", "000") -> Decimal(54),
+ ("404", "000") -> Decimal(404),
+ ("450", "000") -> Decimal(450),
+ ("454", "0000") -> Decimal(454),
+ ("054", "0000") -> Decimal(54),
+ ("404", "0000") -> Decimal(404),
+ ("450", "0000") -> Decimal(450)
+ ).foreach { case ((str, format), expected) =>
+ assert(parse(UTF8String.fromString(str), format) === expected)
+ }
+
+ // Test '.' and 'D'
+ failParseWithInvalidInput(UTF8String.fromString("454.2"), "999",
+ "Format '999' used for parsing string to number or formatting number to
string is invalid")
+ failParseWithInvalidInput(UTF8String.fromString("454.23"), "999.9",
+ "Format '999.9' used for parsing string to number or formatting number
to string is invalid")
+
+ Seq(
+ ("454.2", "999.9") -> Decimal(454.2),
+ ("454.2", "000.0") -> Decimal(454.2),
+ ("454.2", "999D9") -> Decimal(454.2),
+ ("454.2", "000D0") -> Decimal(454.2),
+ ("454.23", "999.99") -> Decimal(454.23),
+ ("454.23", "000.00") -> Decimal(454.23),
+ ("454.23", "999D99") -> Decimal(454.23),
+ ("454.23", "000D00") -> Decimal(454.23),
+ ("454.0", "999.9") -> Decimal(454),
+ ("454.0", "000.0") -> Decimal(454),
+ ("454.0", "999D9") -> Decimal(454),
+ ("454.0", "000D0") -> Decimal(454),
+ ("454.00", "999.99") -> Decimal(454),
+ ("454.00", "000.00") -> Decimal(454),
+ ("454.00", "999D99") -> Decimal(454),
+ ("454.00", "000D00") -> Decimal(454),
+ (".4542", ".9999") -> Decimal(0.4542),
+ (".4542", ".0000") -> Decimal(0.4542),
+ (".4542", "D9999") -> Decimal(0.4542),
+ (".4542", "D0000") -> Decimal(0.4542),
+ ("4542.", "9999.") -> Decimal(4542),
+ ("4542.", "0000.") -> Decimal(4542),
+ ("4542.", "9999D") -> Decimal(4542),
+ ("4542.", "0000D") -> Decimal(4542)
+ ).foreach { case ((str, format), expected) =>
+ assert(parse(UTF8String.fromString(str), format) === expected)
+ }
+
+ failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999.9.9",
+ "Multiple 'D' or '.' in '999.9.9'")
+ failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999D9D9",
+ "Multiple 'D' or '.' in '999D9D9'")
+ failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999.9D9",
+ "Multiple 'D' or '.' in '999.9D9'")
+ failParseWithAnalysisException(UTF8String.fromString("454.3.2"), "999D9.9",
+ "Multiple 'D' or '.' in '999D9.9'")
+
+ // Test ',' and 'G'
+ Seq(
+ ("12,454", "99,999") -> Decimal(12454),
+ ("12,454", "00,000") -> Decimal(12454),
+ ("12,454", "99G999") -> Decimal(12454),
+ ("12,454", "00G000") -> Decimal(12454),
+ ("12,454,367", "99,999,999") -> Decimal(12454367),
+ ("12,454,367", "00,000,000") -> Decimal(12454367),
+ ("12,454,367", "99G999G999") -> Decimal(12454367),
+ ("12,454,367", "00G000G000") -> Decimal(12454367),
+ ("12,454,", "99,999,") -> Decimal(12454),
+ ("12,454,", "00,000,") -> Decimal(12454),
+ ("12,454,", "99G999G") -> Decimal(12454),
+ ("12,454,", "00G000G") -> Decimal(12454),
+ (",454,367", ",999,999") -> Decimal(454367),
+ (",454,367", ",000,000") -> Decimal(454367),
+ (",454,367", "G999G999") -> Decimal(454367),
+ (",454,367", "G000G000") -> Decimal(454367)
+ ).foreach { case ((str, format), expected) =>
+ assert(parse(UTF8String.fromString(str), format) === expected)
+ }
+
+ // Test '$'
+ Seq(
+ ("$78.12", "$99.99") -> Decimal(78.12),
+ ("$78.12", "$00.00") -> Decimal(78.12),
+ ("78.12$", "99.99$") -> Decimal(78.12),
+ ("78.12$", "00.00$") -> Decimal(78.12)
+ ).foreach { case ((str, format), expected) =>
+ assert(parse(UTF8String.fromString(str), format) === expected)
+ }
+
+ failParseWithAnalysisException(UTF8String.fromString("78$.12"), "99$.99",
+ "'$' must be the first or last char in '99$.99'")
+ failParseWithAnalysisException(UTF8String.fromString("$78.12$"), "$99.99$",
+ "Multiple '$' in '$99.99$'")
+
+ // Test '-' and 'S'
+ Seq(
+ ("454-", "999-") -> Decimal(-454),
+ ("454-", "999S") -> Decimal(-454),
+ ("-454", "-999") -> Decimal(-454),
+ ("-454", "S999") -> Decimal(-454),
+ ("454-", "000-") -> Decimal(-454),
+ ("454-", "000S") -> Decimal(-454),
+ ("-454", "-000") -> Decimal(-454),
+ ("-454", "S000") -> Decimal(-454),
+ ("12,454.8-", "99G999D9S") -> Decimal(-12454.8),
+ ("00,454.8-", "99G999.9S") -> Decimal(-454.8)
+ ).foreach { case ((str, format), expected) =>
+ assert(parse(UTF8String.fromString(str), format) === expected)
+ }
+
+ failParseWithAnalysisException(UTF8String.fromString("4-54"), "9S99",
+ "'S' or '-' must be the first or last char in '9S99'")
+ failParseWithAnalysisException(UTF8String.fromString("4-54"), "9-99",
+ "'S' or '-' must be the first or last char in '9-99'")
+ failParseWithAnalysisException(UTF8String.fromString("454.3--"), "999D9SS",
+ "Multiple 'S' or '-' in '999D9SS'")
+ }
+
+ test("format") {
+ assert(format(Decimal(454), "") === "")
+
+ // Test '9' and '0'
+ Seq(
+ (Decimal(454), "9") -> "#",
+ (Decimal(454), "99") -> "##",
+ (Decimal(454), "999") -> "454",
+ (Decimal(54), "999") -> "54",
+ (Decimal(404), "999") -> "404",
+ (Decimal(450), "999") -> "450",
+ (Decimal(454), "9999") -> "454",
+ (Decimal(54), "9999") -> "54",
+ (Decimal(404), "9999") -> "404",
+ (Decimal(450), "9999") -> "450",
+ (Decimal(454), "0") -> "#",
+ (Decimal(454), "00") -> "##",
+ (Decimal(454), "000") -> "454",
+ (Decimal(54), "000") -> "054",
+ (Decimal(404), "000") -> "404",
+ (Decimal(450), "000") -> "450",
+ (Decimal(454), "0000") -> "0454",
+ (Decimal(54), "0000") -> "0054",
+ (Decimal(404), "0000") -> "0404",
+ (Decimal(450), "0000") -> "0450"
+ ).foreach { case ((decimal, str), expected) =>
+ assert(format(decimal, str) === expected)
+ }
+
+ // Test '.' and 'D'
+ Seq(
+ (Decimal(454.2), "999.9") -> "454.2",
+ (Decimal(454.2), "000.0") -> "454.2",
+ (Decimal(454.2), "999D9") -> "454.2",
+ (Decimal(454.2), "000D0") -> "454.2",
+ (Decimal(454), "999.9") -> "454.0",
+ (Decimal(454), "000.0") -> "454.0",
+ (Decimal(454), "999D9") -> "454.0",
+ (Decimal(454), "000D0") -> "454.0",
+ (Decimal(454), "999.99") -> "454.00",
+ (Decimal(454), "000.00") -> "454.00",
+ (Decimal(454), "999D99") -> "454.00",
+ (Decimal(454), "000D00") -> "454.00",
+ (Decimal(0.4542), ".9999") -> ".####",
+ (Decimal(0.4542), ".0000") -> ".####",
+ (Decimal(0.4542), "D9999") -> ".####",
+ (Decimal(0.4542), "D0000") -> ".####",
+ (Decimal(4542), "9999.") -> "4542.",
+ (Decimal(4542), "0000.") -> "4542.",
+ (Decimal(4542), "9999D") -> "4542.",
+ (Decimal(4542), "0000D") -> "4542."
+ ).foreach { case ((decimal, str), expected) =>
+ assert(format(decimal, str) === expected)
+ }
+
+ failFormatWithAnalysisException(Decimal(454.32), "999.9.9",
+ "Multiple 'D' or '.' in '999.9.9'")
+ failFormatWithAnalysisException(Decimal(454.32), "999D9D9",
+ "Multiple 'D' or '.' in '999D9D9'")
+ failFormatWithAnalysisException(Decimal(454.32), "999.9D9",
+ "Multiple 'D' or '.' in '999.9D9'")
+ failFormatWithAnalysisException(Decimal(454.32), "999D9.9",
+ "Multiple 'D' or '.' in '999D9.9'")
+
+ // Test ',' and 'G'
+ Seq(
+ (Decimal(12454), "99,999") -> "12,454",
+ (Decimal(12454), "00,000") -> "12,454",
+ (Decimal(12454), "99G999") -> "12,454",
+ (Decimal(12454), "00G000") -> "12,454",
+ (Decimal(12454367), "99,999,999") -> "12,454,367",
+ (Decimal(12454367), "00,000,000") -> "12,454,367",
+ (Decimal(12454367), "99G999G999") -> "12,454,367",
+ (Decimal(12454367), "00G000G000") -> "12,454,367",
+ (Decimal(12454), "99,999,") -> "12,454,",
+ (Decimal(12454), "00,000,") -> "12,454,",
+ (Decimal(12454), "99G999G") -> "12,454,",
+ (Decimal(12454), "00G000G") -> "12,454,",
+ (Decimal(454367), ",999,999") -> ",454,367",
+ (Decimal(454367), ",000,000") -> ",454,367",
+ (Decimal(454367), "G999G999") -> ",454,367",
+ (Decimal(454367), "G000G000") -> ",454,367"
+ ).foreach { case ((decimal, str), expected) =>
+ assert(format(decimal, str) === expected)
+ }
+
+ // Test '$'
+ Seq(
+ (Decimal(78.12), "$99.99") -> "$78.12",
+ (Decimal(78.12), "$00.00") -> "$78.12",
+ (Decimal(78.12), "99.99$") -> "78.12$",
+ (Decimal(78.12), "00.00$") -> "78.12$"
+ ).foreach { case ((decimal, str), expected) =>
+ assert(format(decimal, str) === expected)
+ }
+
+ failFormatWithAnalysisException(Decimal(78.12), "99$.99",
+ "'$' must be the first or last char in '99$.99'")
+ failFormatWithAnalysisException(Decimal(78.12), "$99.99$",
+ "Multiple '$' in '$99.99$'")
+
+ // Test '-' and 'S'
+ Seq(
+ (Decimal(-454), "999-") -> "454-",
+ (Decimal(-454), "999S") -> "454-",
+ (Decimal(-454), "-999") -> "-454",
+ (Decimal(-454), "S999") -> "-454",
+ (Decimal(-454), "000-") -> "454-",
+ (Decimal(-454), "000S") -> "454-",
+ (Decimal(-454), "-000") -> "-454",
+ (Decimal(-454), "S000") -> "-454",
+ (Decimal(-12454.8), "99G999D9S") -> "12,454.8-",
+ (Decimal(-454.8), "99G999.9S") -> "454.8-"
+ ).foreach { case ((decimal, str), expected) =>
+ assert(format(decimal, str) === expected)
+ }
+
+ failFormatWithAnalysisException(Decimal(-454), "9S99",
+ "'S' or '-' must be the first or last char in '9S99'")
+ failFormatWithAnalysisException(Decimal(-454), "9-99",
+ "'S' or '-' must be the first or last char in '9-99'")
+ failFormatWithAnalysisException(Decimal(-454.3), "999D9SS",
+ "Multiple 'S' or '-' in '999D9SS'")
+ }
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
