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 <[email protected]> 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
