You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/08/27 06:52:07 UTC
[calcite] 03/04: [CALCITE-3280] Add REGEXP_REPLACE function in
Oracle, MySQL libraries (Shuming Li)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit b039b152d8d2e91ab2ca0232ebddfc998f3bb331
Author: ShuMingLi <mi...@gmail.com>
AuthorDate: Mon Aug 26 12:54:12 2019 +0800
[CALCITE-3280] Add REGEXP_REPLACE function in Oracle, MySQL libraries (Shuming Li)
Close apache/calcite#1401
---
.../calcite/adapter/enumerable/RexImpTable.java | 19 ++++++
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 57 ++++++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 5 +-
.../calcite/sql/fun/SqlRegexpReplaceFunction.java | 77 ++++++++++++++++++++++
.../org/apache/calcite/util/BuiltInMethod.java | 8 +++
.../main/java/org/apache/calcite/util/Unsafe.java | 35 ++++++++++
.../calcite/runtime/CalciteResource.properties | 1 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 39 +++++++++++
.../org/apache/calcite/test/SqlFunctionsTest.java | 38 +++++++++++
site/_docs/reference.md | 3 +-
11 files changed, 283 insertions(+), 2 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 6e0ad74..181a9f3 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -106,6 +106,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_TYPE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.LEFT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MONTHNAME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
@@ -450,6 +451,24 @@ public class RexImpTable {
NotImplementor.of(posixRegexImplementor), false);
defineImplementor(SqlStdOperatorTable.NEGATED_POSIX_REGEX_CASE_SENSITIVE, NullPolicy.STRICT,
NotImplementor.of(posixRegexImplementor), false);
+ defineImplementor(REGEXP_REPLACE, NullPolicy.STRICT,
+ new NotNullImplementor() {
+ final NotNullImplementor[] implementors = {
+ new ReflectiveCallNotNullImplementor(
+ BuiltInMethod.REGEXP_REPLACE3.method),
+ new ReflectiveCallNotNullImplementor(
+ BuiltInMethod.REGEXP_REPLACE4.method),
+ new ReflectiveCallNotNullImplementor(
+ BuiltInMethod.REGEXP_REPLACE5.method),
+ new ReflectiveCallNotNullImplementor(
+ BuiltInMethod.REGEXP_REPLACE6.method)
+ };
+ public Expression implement(RexToLixTranslator translator, RexCall call,
+ List<Expression> translatedOperands) {
+ return implementors[call.getOperands().size() - 3]
+ .implement(translator, call, translatedOperands);
+ }
+ }, false);
// Multisets & arrays
defineMethod(CARDINALITY, BuiltInMethod.COLLECTION_SIZE.method,
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index c830eb2..3c39422 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -891,6 +891,9 @@ public interface CalciteResource {
@BaseMessage("Not a valid input for JSON_STORAGE_SIZE: ''{0}''")
ExInst<CalciteException> invalidInputForJsonStorageSize(String value);
+
+ @BaseMessage("Not a valid input for REGEXP_REPLACE: ''{0}''")
+ ExInst<CalciteException> invalidInputForRegexpReplace(String value);
}
// End CalciteResource.java
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 2e23b66..38cf3b6 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -37,6 +37,7 @@ import org.apache.calcite.util.Bug;
import org.apache.calcite.util.NumberUtil;
import org.apache.calcite.util.TimeWithTimeZoneString;
import org.apache.calcite.util.TimestampWithTimeZoneString;
+import org.apache.calcite.util.Unsafe;
import org.apache.calcite.util.Util;
import org.apache.commons.codec.digest.DigestUtils;
@@ -193,6 +194,62 @@ public class SqlFunctions {
return DigestUtils.sha1Hex(string.getBytes());
}
+ /** SQL {@code REGEXP_REPLACE} function with 3 arguments. */
+ public static String regexpReplace(String s, String regex,
+ String replacement) {
+ return regexpReplace(s, regex, replacement, 1, 0, null);
+ }
+
+ /** SQL {@code REGEXP_REPLACE} function with 4 arguments. */
+ public static String regexpReplace(String s, String regex, String replacement,
+ int pos) {
+ return regexpReplace(s, regex, replacement, pos, 0, null);
+ }
+
+ /** SQL {@code REGEXP_REPLACE} function with 5 arguments. */
+ public static String regexpReplace(String s, String regex, String replacement,
+ int pos, int occurrence) {
+ return regexpReplace(s, regex, replacement, pos, occurrence, null);
+ }
+
+ /** SQL {@code REGEXP_REPLACE} function with 6 arguments. */
+ public static String regexpReplace(String s, String regex, String replacement,
+ int pos, int occurrence, String matchType) {
+ if (pos < 1 || pos > s.length()) {
+ throw RESOURCE.invalidInputForRegexpReplace(Integer.toString(pos)).ex();
+ }
+
+ final int flags = makeRegexpFlags(matchType);
+ final Pattern pattern = Pattern.compile(regex, flags);
+
+ return Unsafe.regexpReplace(s, pattern, replacement, pos, occurrence);
+ }
+
+ private static int makeRegexpFlags(String stringFlags) {
+ int flags = 0;
+ if (stringFlags != null) {
+ for (int i = 0; i < stringFlags.length(); ++i) {
+ switch (stringFlags.charAt(i)) {
+ case 'i':
+ flags |= Pattern.CASE_INSENSITIVE;
+ break;
+ case 'c':
+ flags &= ~Pattern.CASE_INSENSITIVE;
+ break;
+ case 'n':
+ flags |= Pattern.DOTALL;
+ break;
+ case 'm':
+ flags |= Pattern.MULTILINE;
+ break;
+ default:
+ throw RESOURCE.invalidInputForRegexpReplace(stringFlags).ex();
+ }
+ }
+ }
+ return flags;
+ }
+
/** SQL SUBSTRING(string FROM ... FOR ...) function. */
public static String substring(String c, int s, int l) {
int lc = c.length();
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 3487ab9..51047fc 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -169,6 +169,9 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {MYSQL})
public static final SqlFunction JSON_STORAGE_SIZE = new SqlJsonStorageSizeFunction();
+ @LibraryOperator(libraries = {MYSQL, ORACLE})
+ public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
+
/** The "MONTHNAME(datetime)" function; returns the name of the month,
* in the current locale, of a TIMESTAMP or DATE argument. */
@LibraryOperator(libraries = {MYSQL})
@@ -235,7 +238,7 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "CONCAT(arg, ...)" function that concatenates strings.
- * For example, "CONCACT('a', 'bc', 'd')" returns "abcd". */
+ * For example, "CONCAT('a', 'bc', 'd')" returns "abcd". */
@LibraryOperator(libraries = {MYSQL, POSTGRESQL, ORACLE})
public static final SqlFunction CONCAT_FUNCTION =
new SqlFunction("CONCAT",
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
new file mode 100644
index 0000000..61a1e67
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
@@ -0,0 +1,77 @@
+/*
+ * 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.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlOperandCountRange;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeTransforms;
+
+/**
+ * The REGEXP_REPLACE(source_string, pattern, replacement [, pos, occurrence, match_type])
+ * searches for a regular expression pattern and replaces every occurrence of the pattern
+ * with the specified string.
+ * */
+public class SqlRegexpReplaceFunction extends SqlFunction {
+
+ public SqlRegexpReplaceFunction() {
+ super("REGEXP_REPLACE",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.VARCHAR),
+ SqlTypeTransforms.TO_NULLABLE),
+ null,
+ null,
+ SqlFunctionCategory.STRING);
+ }
+
+ @Override public SqlOperandCountRange getOperandCountRange() {
+ return SqlOperandCountRanges.between(3, 6);
+ }
+
+ @Override public boolean checkOperandTypes(SqlCallBinding callBinding, boolean throwOnFailure) {
+ final int operandCount = callBinding.getOperandCount();
+ for (int i = 0; i < 3; i++) {
+ if (!OperandTypes.STRING.checkSingleOperandType(
+ callBinding, callBinding.operand(i), 0, throwOnFailure)) {
+ return false;
+ }
+ }
+ for (int i = 3; i < operandCount; i++) {
+ if (i == 3 && !OperandTypes.INTEGER.checkSingleOperandType(
+ callBinding, callBinding.operand(i), 0, throwOnFailure)) {
+ return false;
+ }
+ if (i == 4 && !OperandTypes.INTEGER.checkSingleOperandType(
+ callBinding, callBinding.operand(i), 0, throwOnFailure)) {
+ return false;
+ }
+ if (i == 5 && !OperandTypes.STRING.checkSingleOperandType(
+ callBinding, callBinding.operand(i), 0, throwOnFailure)) {
+ return false;
+ }
+ }
+ return true;
+ }
+}
+
+// End SqlRegexpReplaceFunction.java
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index c274abe..1c4fb53 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -383,6 +383,14 @@ public enum BuiltInMethod {
LIKE(SqlFunctions.class, "like", String.class, String.class),
SIMILAR(SqlFunctions.class, "similar", String.class, String.class),
POSIX_REGEX(SqlFunctions.class, "posixRegex", String.class, String.class, Boolean.class),
+ REGEXP_REPLACE3(SqlFunctions.class, "regexpReplace", String.class,
+ String.class, String.class),
+ REGEXP_REPLACE4(SqlFunctions.class, "regexpReplace", String.class,
+ String.class, String.class, int.class),
+ REGEXP_REPLACE5(SqlFunctions.class, "regexpReplace", String.class,
+ String.class, String.class, int.class, int.class),
+ REGEXP_REPLACE6(SqlFunctions.class, "regexpReplace", String.class,
+ String.class, String.class, int.class, int.class, String.class),
IS_TRUE(SqlFunctions.class, "isTrue", Boolean.class),
IS_NOT_FALSE(SqlFunctions.class, "isNotFalse", Boolean.class),
NOT(SqlFunctions.class, "not", Boolean.class),
diff --git a/core/src/main/java/org/apache/calcite/util/Unsafe.java b/core/src/main/java/org/apache/calcite/util/Unsafe.java
index 61805ab..793db6b 100644
--- a/core/src/main/java/org/apache/calcite/util/Unsafe.java
+++ b/core/src/main/java/org/apache/calcite/util/Unsafe.java
@@ -17,6 +17,8 @@
package org.apache.calcite.util;
import java.io.StringWriter;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
/**
* Contains methods that call JDK methods that the
@@ -49,6 +51,39 @@ public class Unsafe {
// Included in this class because StringBuffer is banned.
sw.getBuffer().setLength(0);
}
+
+ /** Helper for the SQL {@code REGEXP_REPLACE} function.
+ *
+ * <p>It is marked "unsafe" because it uses {@link StringBuffer};
+ * Versions of {@link Matcher#appendReplacement(StringBuffer, String)}
+ * and {@link Matcher#appendTail(StringBuffer)}
+ * that use {@link StringBuilder} are not available until JDK 9. */
+ public static String regexpReplace(String s, Pattern pattern,
+ String replacement, int pos, int occurrence) {
+ Bug.upgrade("when we drop JDK 8, replace StringBuffer with StringBuilder");
+ final StringBuffer sb = new StringBuffer();
+ final String input;
+ if (pos != 1) {
+ sb.append(s, 0, pos - 1);
+ input = s.substring(pos - 1);
+ } else {
+ input = s;
+ }
+
+ int count = 0;
+ Matcher matcher = pattern.matcher(input);
+ while (matcher.find()) {
+ if (occurrence == 0) {
+ matcher.appendReplacement(sb, replacement);
+ } else if (++count == occurrence) {
+ matcher.appendReplacement(sb, replacement);
+ break;
+ }
+ }
+ matcher.appendTail(sb);
+
+ return sb.toString();
+ }
}
// End Unsafe.java
diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index d77353a..1b670b6 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -290,4 +290,5 @@ InvalidInputForJsonLength=Not a valid input for JSON_LENGTH: ''{0}''
InvalidInputForJsonKeys=Not a valid input for JSON_KEYS: ''{0}''
InvalidInputForJsonRemove=Invalid input for JSON_REMOVE: document: ''{0}'', jsonpath expressions: ''{1}''
InvalidInputForJsonStorageSize=Not a valid input for JSON_STORAGE_SIZE: ''{0}''
+InvalidInputForRegexpReplace=Not a valid input for REGEXP_REPLACE: ''{0}''
# End CalciteResource.properties
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index e6cf965..70df001 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -4524,6 +4524,45 @@ public abstract class SqlOperatorBaseTest {
});
}
+ @Test public void testRegexpReplaceFunc() {
+ Stream.of(SqlLibrary.MYSQL, SqlLibrary.ORACLE)
+ .map(this::tester)
+ .forEach(t -> {
+ t.setFor(SqlLibraryOperators.REGEXP_REPLACE);
+ t.checkString("regexp_replace('a b c', 'b', 'X')", "a X c",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X')", "X X X",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('100-200', '(\\d+)', 'num')", "num-num",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('100-200', '(-)', '###')", "100###200",
+ "VARCHAR NOT NULL");
+ t.checkNull("regexp_replace(cast(null as varchar), '(-)', '###')");
+ t.checkNull("regexp_replace('100-200', cast(null as varchar), '###')");
+ t.checkNull("regexp_replace('100-200', '(-)', cast(null as varchar))");
+ t.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 2)", "aX X X",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 1, 3)", "abc def X",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'c')", "abc def GHI",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'i')", "abc def X",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'i')", "abc def X",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc\t\ndef\t\nghi', '\t', '+')", "abc+\ndef+\nghi",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc\t\ndef\t\nghi', '\t\n', '+')", "abc+def+ghi",
+ "VARCHAR NOT NULL");
+ t.checkString("regexp_replace('abc\t\ndef\t\nghi', '\\w+', '+')", "+\t\n+\t\n+",
+ "VARCHAR NOT NULL");
+ t.checkQuery("select regexp_replace('a b c', 'b', 'X')");
+ t.checkQuery("select regexp_replace('a b c', 'b', 'X', 1)");
+ t.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3)");
+ t.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3, 'i')");
+ });
+ }
+
@Test public void testJsonExists() {
tester.checkBoolean("json_exists('{\"foo\":\"bar\"}', "
+ "'strict $.foo' false on error)", Boolean.TRUE);
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 69c3963..0ec605d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -42,6 +42,7 @@ import static org.apache.calcite.runtime.SqlFunctions.lower;
import static org.apache.calcite.runtime.SqlFunctions.ltrim;
import static org.apache.calcite.runtime.SqlFunctions.md5;
import static org.apache.calcite.runtime.SqlFunctions.posixRegex;
+import static org.apache.calcite.runtime.SqlFunctions.regexpReplace;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.sha1;
import static org.apache.calcite.runtime.SqlFunctions.subtractMonths;
@@ -102,6 +103,43 @@ public class SqlFunctionsTest {
assertThat(posixRegex("abcq", "[[:xdigit:]]", false), is(true));
}
+ @Test public void testRegexpReplace() {
+ assertThat(regexpReplace("a b c", "b", "X"), is("a X c"));
+ assertThat(regexpReplace("abc def ghi", "[g-z]+", "X"), is("abc def X"));
+ assertThat(regexpReplace("abc def ghi", "[a-z]+", "X"), is("X X X"));
+ assertThat(regexpReplace("a b c", "a|b", "X"), is("X X c"));
+ assertThat(regexpReplace("a b c", "y", "X"), is("a b c"));
+
+ assertThat(regexpReplace("100-200", "(\\d+)", "num"), is("num-num"));
+ assertThat(regexpReplace("100-200", "(\\d+)", "###"), is("###-###"));
+ assertThat(regexpReplace("100-200", "(-)", "###"), is("100###200"));
+
+ assertThat(regexpReplace("abc def ghi", "[a-z]+", "X", 1), is("X X X"));
+ assertThat(regexpReplace("abc def ghi", "[a-z]+", "X", 2), is("aX X X"));
+ assertThat(regexpReplace("abc def ghi", "[a-z]+", "X", 1, 3),
+ is("abc def X"));
+ assertThat(regexpReplace("abc def GHI", "[a-z]+", "X", 1, 3, "c"),
+ is("abc def GHI"));
+ assertThat(regexpReplace("abc def GHI", "[a-z]+", "X", 1, 3, "i"),
+ is("abc def X"));
+
+ try {
+ regexpReplace("abc def ghi", "[a-z]+", "X", 0);
+ fail("'regexp_replace' on an invalid pos is not possible");
+ } catch (CalciteException e) {
+ assertThat(e.getMessage(),
+ is("Not a valid input for REGEXP_REPLACE: '0'"));
+ }
+
+ try {
+ regexpReplace("abc def ghi", "[a-z]+", "X", 1, 3, "WWW");
+ fail("'regexp_replace' on an invalid matchType is not possible");
+ } catch (CalciteException e) {
+ assertThat(e.getMessage(),
+ is("Not a valid input for REGEXP_REPLACE: 'WWW'"));
+ }
+ }
+
@Test public void testLower() {
assertThat(lower("A bCd Iijk"), is("a bcd iijk"));
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 0ae90ef..c5efae8 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2193,6 +2193,7 @@ semantics.
| m p | MD5(string) | Calculates an MD5 128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in the connection's locale, of the month in *datetime*; for example, it returns '二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10'
| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
+| m o | REGEXP_REPLACE(string, regexp, rep, [, pos [, occurrence [, matchType]]]) | Replaces all substrings of *string* that match *regexp* with *rep* at the starting *pos* in expr (if omitted, the default is 1), *occurrence* means which occurrence of a match to search for (if omitted, the default is 1), *matchType* specifies how to perform matching
| m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1
| m | REVERSE(string) | Returns *string* with the order of the characters reversed
| m p | RIGHT(string, length) | Returns the rightmost *length* characters from the *string*
@@ -2200,7 +2201,7 @@ semantics.
| m p | SHA1(string) | Calculates a SHA-1 hash value of *string* and returns it as a hex string
| m o p | SOUNDEX(string) | Returns the phonetic representation of *string*; throws if *string* is encoded with multi-byte encoding such as UTF-8
| m | SPACE(integer) | Returns a string of *integer* spaces; returns an empty string if *integer* is less than 1
-| o | SUBSTR(string, position [, substring_length ]) | Returns a portion of *string*, beginning at character *position*, *substring_length* characters long. SUBSTR calculates lengths using characters as defined by the input character set
+| o | SUBSTR(string, position [, substringLength ]) | Returns a portion of *string*, beginning at character *position*, *substringLength* characters long. SUBSTR calculates lengths using characters as defined by the input character set
| o p | TO_DATE(string, format) | Converts *string* to a date using the format *format*
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a timestamp using the format *format*
| o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced