This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 0c6a3c1fac [CALCITE-6310] Add REGEXP_REPLACE function (enabled in
PostgreSQL library)
0c6a3c1fac is described below
commit 0c6a3c1face14cf8136a593ec25a7f13631a09df
Author: James Duong <[email protected]>
AuthorDate: Mon May 27 10:53:55 2024 -0700
[CALCITE-6310] Add REGEXP_REPLACE function (enabled in PostgreSQL library)
Reworked the overloading of REGEXP_REPLACE:
* There is a two argument version that is only for Amazon Redshift,
replacement string is the empty string
* There is a three argument version that is for everything except PostgreSQL
* PostgreSQL has its own 3 and 4 argument versions
* PostgreSQL 3 argument version will only replace first occurrence
* The g flag is now support for the 4 argument function in PostgreSQL
* BigQuery only supports a 3 argument version REGEXP_REPLACE
* BigQuery uses different syntax to refer to capture groups
* There is a new REGEXP_REPLACE for BigQuery
* No longer need to examine the ConformanceEnum to pick the correct
implementation
* MySQL and Amazon Redshift have two signatures with 5 arguments
* Oracle only has one signature with 5 arguments
---
babel/src/test/resources/sql/postgresql.iq | 15 ++
babel/src/test/resources/sql/redshift.iq | 10 ++
.../calcite/adapter/enumerable/RexImpTable.java | 52 +++----
.../org/apache/calcite/runtime/SqlFunctions.java | 29 +++-
.../calcite/sql/fun/SqlLibraryOperators.java | 102 ++++++++++++-
.../calcite/sql/fun/SqlRegexpReplaceFunction.java | 75 ---------
.../org/apache/calcite/sql/type/OperandTypes.java | 4 +
.../sql/validate/SqlAbstractConformance.java | 4 -
.../calcite/sql/validate/SqlConformance.java | 16 --
.../calcite/sql/validate/SqlConformanceEnum.java | 9 --
.../sql/validate/SqlDelegatingConformance.java | 4 -
.../org/apache/calcite/util/BuiltInMethod.java | 12 +-
.../apache/calcite/util/RelToSqlConverterUtil.java | 4 +-
.../org/apache/calcite/test/SqlFunctionsTest.java | 6 +
.../org/apache/calcite/test/SqlValidatorTest.java | 27 ++++
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 169 +++++++++++++++++----
17 files changed, 365 insertions(+), 175 deletions(-)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index d2e0b53224..dc8a98d7ca 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -1231,4 +1231,19 @@ X
1
!ok
+SELECT regexp_replace('abc def GHI', '[a-z]+', 'X') AS x;
+X
+X def GHI
+!ok
+
+SELECT regexp_replace('abc def GHI', '[a-z]+', 'X', 'g') AS x;
+X
+X X GHI
+!ok
+
+SELECT regexp_replace('ABC def GHI', '[a-z]+', 'X', 'i') AS x;
+X
+X def GHI
+!ok
+
# End postgresql.iq
diff --git a/babel/src/test/resources/sql/redshift.iq
b/babel/src/test/resources/sql/redshift.iq
index ca0ed2fb9f..6104222746 100755
--- a/babel/src/test/resources/sql/redshift.iq
+++ b/babel/src/test/resources/sql/redshift.iq
@@ -2232,6 +2232,16 @@ USER
sa
!ok
+SELECT regexp_replace('abcabc', 'b') AS x;
+X
+acac
+!ok
+
+SELECT regexp_replace('abc def GHI', '[a-z]+', 'X') AS x;
+X
+X X GHI
+!ok
+
# VERSION
# Returns details about the currently installed release,
# with specific Amazon Redshift version information at the end.
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 e20e4a82f2..55ef175c67 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
@@ -254,7 +254,15 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_EXTRACT;
import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_EXTRACT_ALL;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_INSTR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_LIKE;
-import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_2;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_3;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_4;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_5;
+import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_5_ORACLE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_6;
+import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_BIG_QUERY_3;
+import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_PG_3;
+import static
org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_PG_4;
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.REVERSE_SPARK;
@@ -864,7 +872,16 @@ public class RexImpTable {
NotImplementor.of(insensitiveImplementor));
map.put(NEGATED_POSIX_REGEX_CASE_SENSITIVE,
NotImplementor.of(sensitiveImplementor));
- map.put(REGEXP_REPLACE, new RegexpReplaceImplementor());
+ defineReflective(REGEXP_REPLACE_2, BuiltInMethod.REGEXP_REPLACE2.method);
+ defineReflective(REGEXP_REPLACE_3, BuiltInMethod.REGEXP_REPLACE3.method);
+ defineReflective(REGEXP_REPLACE_4, BuiltInMethod.REGEXP_REPLACE4.method);
+ defineReflective(REGEXP_REPLACE_5,
BuiltInMethod.REGEXP_REPLACE5_OCCURRENCE.method,
+ BuiltInMethod.REGEXP_REPLACE5_MATCHTYPE.method);
+ defineReflective(REGEXP_REPLACE_5_ORACLE,
BuiltInMethod.REGEXP_REPLACE5_OCCURRENCE.method);
+ defineReflective(REGEXP_REPLACE_6, BuiltInMethod.REGEXP_REPLACE6.method);
+ defineReflective(REGEXP_REPLACE_BIG_QUERY_3,
BuiltInMethod.REGEXP_REPLACE_BIG_QUERY_3.method);
+ defineReflective(REGEXP_REPLACE_PG_3,
BuiltInMethod.REGEXP_REPLACE_PG_3.method);
+ defineReflective(REGEXP_REPLACE_PG_4,
BuiltInMethod.REGEXP_REPLACE_PG_4.method);
// Multisets & arrays
@@ -2512,37 +2529,6 @@ public class RexImpTable {
}
}
- /** Implementor for the {@code REGEXP_REPLACE} function. */
- private static class RegexpReplaceImplementor extends
AbstractRexCallImplementor {
- RegexpReplaceImplementor() {
- super("regexp_replace", NullPolicy.STRICT, false);
- }
-
- @Override Expression implementSafe(final RexToLixTranslator translator,
- final RexCall call, final List<Expression> argValueList) {
- // Boolean indicating if dialect uses default $-based indexing for
- // regex capturing group (false means double-backslash-based indexing)
- final boolean dollarIndexed =
- translator.conformance.isRegexReplaceCaptureGroupDollarIndexed();
-
- // Standard REGEXP_REPLACE implementation for default indexing.
- if (dollarIndexed) {
- final ReflectiveImplementor implementor =
- new ReflectiveImplementor(
- ImmutableList.of(BuiltInMethod.REGEXP_REPLACE3.method,
- BuiltInMethod.REGEXP_REPLACE4.method,
- BuiltInMethod.REGEXP_REPLACE5.method,
- BuiltInMethod.REGEXP_REPLACE6.method));
- return implementor.implementSafe(translator, call, argValueList);
- }
-
- // Custom regexp replace method to preprocess double-backslashes into
$-based indices.
- return
Expressions.call(Expressions.new_(SqlFunctions.RegexFunction.class),
- "regexpReplaceNonDollarIndexed",
- argValueList);
- }
- }
-
/** Implementor for the {@code MONTHNAME} and {@code DAYNAME} functions.
* Each takes a {@link java.util.Locale} argument. */
private static class PeriodNameImplementor extends
AbstractRexCallImplementor {
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 65bc1a0142..ff51fc6b66 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -632,6 +632,11 @@ public class SqlFunctions {
return matchIndex;
}
+ /** SQL {@code REGEXP_REPLACE} function with 2 arguments. */
+ public String regexpReplace(String s, String regex) {
+ return regexpReplace(s, regex, "", 1, 0, null);
+ }
+
/** SQL {@code REGEXP_REPLACE} function with 3 arguments. */
public String regexpReplace(String s, String regex,
String replacement) {
@@ -644,12 +649,18 @@ public class SqlFunctions {
return regexpReplace(s, regex, replacement, pos, 0, null);
}
- /** SQL {@code REGEXP_REPLACE} function with 5 arguments. */
+ /** SQL {@code REGEXP_REPLACE} function with 5 arguments. Last argument is
occurrence. */
public 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 5 arguments. Last argument is
match type */
+ public String regexpReplace(String s, String regex, String replacement,
+ int pos, String matchType) {
+ return regexpReplace(s, regex, replacement, pos, 0, matchType);
+ }
+
/** SQL {@code REGEXP_REPLACE} function with 6 arguments. */
public String regexpReplace(String s, String regex, String replacement,
int pos, int occurrence, @Nullable String matchType) {
@@ -663,6 +674,18 @@ public class SqlFunctions {
return Unsafe.regexpReplace(s, pattern, replacement, pos, occurrence);
}
+ /** SQL {@code REGEXP_REPLACE} function for PostgreSQL with 3 arguments. */
+ public String regexpReplacePg(String s, String regex, String replacement) {
+ return regexpReplace(s, regex, replacement, 1, 1, null);
+ }
+
+ /** SQL {@code REGEXP_REPLACE} function for PostgreSQL with 4 arguments. */
+ public String regexpReplacePg(String s, String regex, String replacement,
String matchType) {
+ // Translate g flag to occurrence
+ final int occurrence = matchType.contains("g") ? 0 : 1;
+ return regexpReplace(s, regex, replacement, 1, occurrence, matchType);
+ }
+
/** SQL {@code REGEXP_REPLACE} function with 3 arguments with
* {@code \\} based indexing for capturing groups. */
public String regexpReplaceNonDollarIndexed(String s, String regex,
@@ -706,6 +729,10 @@ public class SqlFunctions {
// for consistency.
flags &= ~Pattern.DOTALL;
break;
+ case 'g':
+ // This flag is in PostgreSQL but doesn't apply to other libraries.
Skip here since
+ // this is actually occurrence.
+ break;
default:
throw RESOURCE.invalidInputForRegexpReplace(stringFlags).ex();
}
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 df3dcbd002..a3069cec11 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
@@ -565,11 +565,107 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
SqlFunctionCategory.STRING);
- /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [,
matchType]]])"
+ /** The "REGEXP_REPLACE(value, regexp)"
* function. Replaces all substrings of value that match regexp with
* {@code rep} and returns modified value. */
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
- public static final SqlFunction REGEXP_REPLACE = new
SqlRegexpReplaceFunction();
+ @LibraryOperator(libraries = {REDSHIFT})
+ public static final SqlFunction REGEXP_REPLACE_2 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+ SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC,
false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. */
+ @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+ public static final SqlFunction REGEXP_REPLACE_3 =
+ SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+ OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
+ /** The "REGEXP_REPLACE(value, regexp, rep, pos)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. Start searching value from
character position
+ * pos. */
+ @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+ public static final SqlFunction REGEXP_REPLACE_4 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.family(SqlTypeFamily.STRING,
SqlTypeFamily.STRING,
+ SqlTypeFamily.STRING, SqlTypeFamily.INTEGER),
+ 0, SqlFunctionCategory.STRING, call ->
SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep, pos, [ occurrence | matchType ])"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. Start searching value from
character position
+ * pos. Replace only the occurrence match or all matches if occurrence is 0.
matchType
+ * is a string of flags to apply to the search. */
+ @LibraryOperator(libraries = {MYSQL, REDSHIFT})
+ public static final SqlFunction REGEXP_REPLACE_5 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT,
+ OperandTypes.or(
+ OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.STRING,
+ SqlTypeFamily.STRING, SqlTypeFamily.INTEGER,
SqlTypeFamily.INTEGER),
+ OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.STRING,
+ SqlTypeFamily.STRING, SqlTypeFamily.INTEGER,
SqlTypeFamily.STRING)),
+ 0, SqlFunctionCategory.STRING, call ->
SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep, pos, matchType)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. Start searching value from
character position
+ * pos. Replace only the occurrence match or all matches if occurrence is 0.
*/
+ @LibraryOperator(libraries = {ORACLE})
+ public static final SqlFunction REGEXP_REPLACE_5_ORACLE =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.family(SqlTypeFamily.STRING,
SqlTypeFamily.STRING,
+ SqlTypeFamily.STRING, SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER),
+ 0, SqlFunctionCategory.STRING, call ->
SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep, pos, occurrence, matchType)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. Start searching value from
character position
+ * pos. Replace only the occurrence match or all matches if occurrence is 0.
matchType
+ * is a string of flags to apply to the search. */
+ @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+ public static final SqlFunction REGEXP_REPLACE_6 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.family(SqlTypeFamily.STRING,
SqlTypeFamily.STRING,
+ SqlTypeFamily.STRING, SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER,
SqlTypeFamily.STRING),
+ 0, SqlFunctionCategory.STRING, call ->
SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction REGEXP_REPLACE_BIG_QUERY_3 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING, 0,
+ SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC,
false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. */
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = REDSHIFT)
+ public static final SqlFunction REGEXP_REPLACE_PG_3 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING, 0,
+ SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC,
false) { };
+
+ /** The "REGEXP_REPLACE(value, regexp, rep, flags)"
+ * function. Replaces all substrings of value that match regexp with
+ * {@code rep} and returns modified value. flags are applied to the search.
*/
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = REDSHIFT)
+ public static final SqlFunction REGEXP_REPLACE_PG_4 =
+ new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING_STRING, 0,
+ SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC,
false) { };
/** The "REGEXP_SUBSTR(value, regexp[, position[, occurrence]])" function.
* Returns the substring in value that matches the regexp. Returns NULL if
there is no match. */
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
deleted file mode 100644
index e56e74e2ec..0000000000
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
+++ /dev/null
@@ -1,75 +0,0 @@
-/*
- * 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.SqlTypeFamily;
-
-import java.util.ArrayList;
-import java.util.List;
-
-/**
- * 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.VARCHAR_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();
- assert operandCount >= 3;
- if (operandCount == 3) {
- return OperandTypes.STRING_STRING_STRING
- .checkOperandTypes(callBinding, throwOnFailure);
- }
- final List<SqlTypeFamily> families = new ArrayList<>();
- families.add(SqlTypeFamily.STRING);
- families.add(SqlTypeFamily.STRING);
- families.add(SqlTypeFamily.STRING);
- for (int i = 3; i < operandCount; i++) {
- if (i == 3) {
- families.add(SqlTypeFamily.INTEGER);
- }
- if (i == 4) {
- families.add(SqlTypeFamily.INTEGER);
- }
- if (i == 5) {
- families.add(SqlTypeFamily.STRING);
- }
- }
- return OperandTypes.family(families.toArray(new SqlTypeFamily[0]))
- .checkOperandTypes(callBinding, throwOnFailure);
- }
-}
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 1bdc66f420..5ba35b889b 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -465,6 +465,10 @@ public abstract class OperandTypes {
// Second and Third operand both are optional (operand index 0, 1, 2)
number -> number == 1 || number == 2);
+ public static final FamilyOperandTypeChecker STRING_STRING_STRING_STRING =
+ family(SqlTypeFamily.STRING, SqlTypeFamily.STRING, SqlTypeFamily.STRING,
+ SqlTypeFamily.STRING);
+
public static final FamilyOperandTypeChecker STRING_NUMERIC_OPTIONAL_STRING =
family(
ImmutableList.of(SqlTypeFamily.STRING, SqlTypeFamily.NUMERIC,
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 14c2c73e35..3205a48b40 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -105,10 +105,6 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
return SqlConformanceEnum.DEFAULT.isOffsetLimitAllowed();
}
- @Override public boolean isRegexReplaceCaptureGroupDollarIndexed() {
- return
SqlConformanceEnum.DEFAULT.isRegexReplaceCaptureGroupDollarIndexed();
- }
-
@Override public boolean isPercentRemainderAllowed() {
return SqlConformanceEnum.DEFAULT.isPercentRemainderAllowed();
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index 896db041e8..b8501f02f1 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -256,22 +256,6 @@ public interface SqlConformance {
*/
boolean isMinusAllowed();
- /**
- * Whether this dialect uses {@code $} (dollar) for indexing capturing groups
- * in the replacement string of regular expression functions such as
- * {@code REGEXP_REPLACE}. If false, the dialect uses {@code \\} (backslash)
- * for indexing capturing groups.
- *
- * <p>For example, {@code REGEXP_REPLACE("abc", "a(.)c", "X\\1")} in BigQuery
- * is equivalent to {@code REGEXP_REPLACE("abc", "a(.)c", "X$1")} in MySQL;
- * both produce the result "Xb".
- *
- * <p>Among the built-in conformance levels, false in
- * {@link SqlConformanceEnum#BIG_QUERY};
- * true otherwise.
- */
- boolean isRegexReplaceCaptureGroupDollarIndexed();
-
/**
* Whether {@code CROSS APPLY} and {@code OUTER APPLY} operators are allowed
* in the parser.
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 1b7bd1a811..a610f19f16 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -240,15 +240,6 @@ public enum SqlConformanceEnum implements SqlConformance {
}
}
- @Override public boolean isRegexReplaceCaptureGroupDollarIndexed() {
- switch (this) {
- case BIG_QUERY:
- return false;
- default:
- return true;
- }
- }
-
@Override public boolean isPercentRemainderAllowed() {
switch (this) {
case BABEL:
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index 7b60bb4445..c8e2f7cdd2 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -87,10 +87,6 @@ public class SqlDelegatingConformance implements
SqlConformance {
return delegate.isMinusAllowed();
}
- @Override public boolean isRegexReplaceCaptureGroupDollarIndexed() {
- return delegate.isRegexReplaceCaptureGroupDollarIndexed();
- }
-
@Override public boolean isApplyAllowed() {
return delegate.isApplyAllowed();
}
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 3dd78e8772..bc5ca1d61a 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -586,15 +586,25 @@ public enum BuiltInMethod {
String.class, String.class, int.class, int.class, int.class),
REGEXP_LIKE3(SqlFunctions.RegexFunction.class, "regexpLike",
String.class, String.class, String.class),
+ REGEXP_REPLACE2(SqlFunctions.RegexFunction.class, "regexpReplace",
+ String.class, String.class),
REGEXP_REPLACE3(SqlFunctions.RegexFunction.class, "regexpReplace",
String.class, String.class, String.class),
REGEXP_REPLACE4(SqlFunctions.RegexFunction.class, "regexpReplace",
String.class, String.class, String.class, int.class),
- REGEXP_REPLACE5(SqlFunctions.RegexFunction.class, "regexpReplace",
+ REGEXP_REPLACE5_OCCURRENCE(SqlFunctions.RegexFunction.class, "regexpReplace",
String.class, String.class, String.class, int.class, int.class),
+ REGEXP_REPLACE5_MATCHTYPE(SqlFunctions.RegexFunction.class, "regexpReplace",
+ String.class, String.class, String.class, int.class, String.class),
REGEXP_REPLACE6(SqlFunctions.RegexFunction.class, "regexpReplace",
String.class, String.class, String.class, int.class, int.class,
String.class),
+ REGEXP_REPLACE_BIG_QUERY_3(SqlFunctions.RegexFunction.class,
"regexpReplaceNonDollarIndexed",
+ String.class, String.class, String.class),
+ REGEXP_REPLACE_PG_3(SqlFunctions.RegexFunction.class, "regexpReplacePg",
+ String.class, String.class, String.class),
+ REGEXP_REPLACE_PG_4(SqlFunctions.RegexFunction.class, "regexpReplacePg",
+ String.class, String.class, String.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/RelToSqlConverterUtil.java
b/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
index 36816a2bfa..6b510e4141 100644
--- a/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
+++ b/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
@@ -26,7 +26,7 @@ import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.parser.SqlParserPos;
-import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_3;
import static java.util.Objects.requireNonNull;
@@ -58,7 +58,7 @@ public abstract class RelToSqlConverterUtil {
final SqlCharStringLiteral blankLiteral =
SqlLiteral.createCharString("", call.getParserPosition());
final SqlNode[] trimOperands = new SqlNode[] { call.operand(2),
regexNode, blankLiteral };
- final SqlCall regexReplaceCall =
REGEXP_REPLACE.createCall(SqlParserPos.ZERO, trimOperands);
+ final SqlCall regexReplaceCall =
REGEXP_REPLACE_3.createCall(SqlParserPos.ZERO, trimOperands);
regexReplaceCall.unparse(writer, leftPrec, rightPrec);
}
}
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 c2dab83304..0f9f9049ed 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -495,6 +495,7 @@ class SqlFunctionsTest {
@Test void testRegexpReplace() {
final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();
+ assertThat(f.regexpReplace("abc", "b"), is("ac"));
assertThat(f.regexpReplace("a b c", "b", "X"), is("a X c"));
assertThat(f.regexpReplace("abc def ghi", "[g-z]+", "X"), is("abc def X"));
assertThat(f.regexpReplace("abc def ghi", "[a-z]+", "X"), is("X X X"));
@@ -513,6 +514,11 @@ class SqlFunctionsTest {
is("abc def GHI"));
assertThat(f.regexpReplace("abc def GHI", "[a-z]+", "X", 1, 3, "i"),
is("abc def X"));
+ assertThat(f.regexpReplacePg("abc def GHI", "[a-z]+", "X"), is("X def
GHI"));
+ assertThat(f.regexpReplacePg("abc def GHI", "[a-z]+", "X", "g"),
+ is("X X GHI"));
+ assertThat(f.regexpReplacePg("ABC def GHI", "[a-z]+", "X", "i"),
+ is("X def GHI"));
try {
f.regexpReplace("abc def ghi", "[a-z]+", "X", 0);
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 927274d21c..2ff73fb2ed 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -11946,6 +11946,33 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.columnType("VARCHAR NOT NULL");
}
+ @Test void testPgRegexpReplace() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.POSTGRESQL);
+
+ expr("REGEXP_REPLACE('a b c', 'a', 'X')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR NOT NULL");
+ expr("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR NOT NULL");
+ expr("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR NOT NULL");
+ expr("REGEXP_REPLACE('abc def GHI', '[a-z]+', 'X', 'c')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR NOT NULL");
+ // Implicit type coercion.
+ expr("REGEXP_REPLACE(null, '(-)', '###')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR");
+ expr("REGEXP_REPLACE('100-200', null, '###')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR");
+ expr("REGEXP_REPLACE('100-200', '(-)', null)")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR");
+ }
+
@Test void testInvalidFunctionCall() {
final SqlOperatorTable operatorTable =
MockSqlOperatorTable.standard().extend();
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 180c3aab51..b3565bc1fa 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2854,7 +2854,9 @@ In the following:
| b | REGEXP_EXTRACT_ALL(string, regexp) | Returns an array of all
substrings in *string* that matches the *regexp*. Returns an empty array if
there is no match
| b | REGEXP_INSTR(string, regexp [, position [, occurrence [,
occurrence_position]]]) | Returns the lowest 1-based position of the substring
in *string* that matches the *regexp*, starting search at *position* (default
1), and until locating the nth *occurrence* (default 1). Setting
occurrence_position (default 0) to 1 returns the end position of substring + 1.
Returns 0 if there is no match
| m o p r s | REGEXP_LIKE(string, regexp [, flags]) | Equivalent to `string1
RLIKE string2` with an optional parameter for search flags. Supported flags
are: <ul><li>i: case-insensitive matching</li><li>c: case-sensitive
matching</li><li>n: newline-sensitive matching</li><li>s: non-newline-sensitive
matching</li><li>m: multi-line</li></ul>
+| r | REGEXP_REPLACE(string, regexp) | Replaces all substrings
of *string* that match *regexp* with the empty string
| b m o r | 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* specifies which occurrence of a match to search for (if omitted,
the default is 1), *matchType* specifies how to perform matching
+| p | REGEXP_REPLACE(string, regexp, rep [, matchType]) | Replaces substrings
of *string* that match *regexp* with *rep* at the starting *pos* in expr,
*matchType* specifies how to perform matching and whether to only replace first
match or all
| b | REGEXP_SUBSTR(string, regexp [, position [, occurrence]]) | Synonym for
REGEXP_EXTRACT
| b m p r s | REPEAT(string, integer) | Returns a string
consisting of *string* repeated of *integer* times; returns an empty string if
*integer* is less than 1
| b m | REVERSE(string) | Returns *string* with
the order of the characters reversed
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 4427dcd83f..7e880e94c6 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5829,10 +5829,32 @@ public class SqlOperatorTest {
f.checkQuery("select regexp_instr('a9cadca5c4aecghi', 'a[0-9]c', 1, 3)");
}
- @Test void testRegexpReplaceFunc() {
+ @Test void testRegexpReplace2Func() {
final SqlOperatorFixture f0 = fixture();
final Consumer<SqlOperatorFixture> consumer = f -> {
- f.setFor(SqlLibraryOperators.REGEXP_REPLACE);
+ f.setFor(SqlLibraryOperators.REGEXP_REPLACE_2);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('a b c', 'b')", "a c",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('abc1 def2 ghi3', '[a-z]+')", "1 2 3",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('100-200', '(\\d+)')", "-",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('100-200', '(-)')", "100200",
+ "VARCHAR NOT NULL");
+
+ f.checkQuery("select regexp_replace('a b c', 'b')");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
+ }
+
+ @Test void testRegexpReplace3Func() {
+ final SqlOperatorFixture f0 = fixture();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.setFor(SqlLibraryOperators.REGEXP_REPLACE_3);
// Tests for regexp replace generic functionality
f.checkString("regexp_replace('a b c', 'b', 'X')", "a X c",
@@ -5846,16 +5868,6 @@ public class SqlOperatorTest {
f.checkNull("regexp_replace(cast(null as varchar), '(-)', '###')");
f.checkNull("regexp_replace('100-200', cast(null as varchar), '###')");
f.checkNull("regexp_replace('100-200', '(-)', cast(null as varchar))");
- f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 2)", "aX X
X",
- "VARCHAR NOT NULL");
- f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 1, 3)", "abc
def X",
- "VARCHAR NOT NULL");
- f.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'c')",
"abc def GHI",
- "VARCHAR NOT NULL");
- f.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'i')",
"abc def X",
- "VARCHAR NOT NULL");
- f.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'i')",
"abc def X",
- "VARCHAR NOT NULL");
f.checkString("regexp_replace('abc\t\ndef\t\nghi', '\t', '+')",
"abc+\ndef+\nghi",
"VARCHAR NOT NULL");
f.checkString("regexp_replace('abc\t\ndef\t\nghi', '\t\n', '+')",
"abc+def+ghi",
@@ -5864,38 +5876,141 @@ public class SqlOperatorTest {
"VARCHAR NOT NULL");
f.checkQuery("select regexp_replace('a b c', 'b', 'X')");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE,
SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
+
+ // Tests to verify double-backslashes are ignored for indexing in other
dialects
+ final SqlOperatorFixture f2 =
+
f0.withLibrary(SqlLibrary.MYSQL).withConformance(SqlConformanceEnum.MYSQL_5);
+ f2.checkString("regexp_replace('abc16', 'b(.*)(\\d)', '\\\\2\\\\1X')",
"a\\2\\1X",
+ "VARCHAR NOT NULL");
+ f2.checkString("regexp_replace('abcdefghijabc', 'abc(.)', '\\\\-11x')",
"\\-11xefghijabc",
+ "VARCHAR NOT NULL");
+ f2.checkString("regexp_replace('abcdefghijabc', 'abc(.)', '$1x')",
"dxefghijabc",
+ "VARCHAR NOT NULL");
+ }
+
+ @Test void testRegexpReplace4Func() {
+ final SqlOperatorFixture f0 = fixture();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.setFor(SqlLibraryOperators.REGEXP_REPLACE_4);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 2)", "aX X
X",
+ "VARCHAR NOT NULL");
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1)");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.MYSQL, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
+ }
+
+ @Test void testRegexpReplace5Func() {
+ final SqlOperatorFixture f0 = fixture();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.setFor(SqlLibraryOperators.REGEXP_REPLACE_5);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 1, 3)", "abc
def X",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('ABC def ghi', '[a-z]+', 'X', 1, 'i')", "X
X X",
+ "VARCHAR NOT NULL");
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3)");
+ f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 'i')");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.MYSQL, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
+ }
+
+ @Test void testRegexpReplace5OracleFunc() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.ORACLE);
+ f.setFor(SqlLibraryOperators.REGEXP_REPLACE_5_ORACLE);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 1, 3)", "abc
def X",
+ "VARCHAR NOT NULL");
+ f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 1)");
+ }
+
+ @Test void testRegexpReplace6Func() {
+ final SqlOperatorFixture f0 = fixture();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.setFor(SqlLibraryOperators.REGEXP_REPLACE_6);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'c')",
"abc def GHI",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 1, 3, 'i')",
"abc def X",
+ "VARCHAR NOT NULL");
+
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3, 'i')");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE,
- SqlLibrary.REDSHIFT);
+ list(SqlLibrary.MYSQL, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT);
f0.forEachLibrary(libraries, consumer);
+ }
+
+ @Test void testRegexpReplaceBigQuery3Func() {
+ final SqlOperatorFixture f =
fixture().setFor(SqlLibraryOperators.REGEXP_REPLACE_BIG_QUERY_3)
+ .withLibrary(SqlLibrary.BIG_QUERY);
// Tests for double-backslash indexed capturing groups for regexp_replace
in BQ
- final SqlOperatorFixture f1 =
-
f0.withLibrary(SqlLibrary.BIG_QUERY).withConformance(SqlConformanceEnum.BIG_QUERY);
- f1.checkString("regexp_replace('abc16', 'b(.*)(\\d)', '\\\\2\\\\1X')",
"a6c1X",
+ f.checkString("regexp_replace('abc16', 'b(.*)(\\d)', '\\\\2\\\\1X')",
"a6c1X",
"VARCHAR NOT NULL");
- f1.checkString("regexp_replace('a\\bc56a\\bc37', 'b(.)(\\d)',
'\\\\2\\\\0X')",
+ f.checkString("regexp_replace('a\\bc56a\\bc37', 'b(.)(\\d)',
'\\\\2\\\\0X')",
"a\\5bc5X6a\\3bc3X7", "VARCHAR NOT NULL");
- f1.checkString("regexp_replace('abcdefghijabc', 'abc(.)',
'\\\\\\\\123xyz')",
+ f.checkString("regexp_replace('abcdefghijabc', 'abc(.)',
'\\\\\\\\123xyz')",
"\\123xyzefghijabc", "VARCHAR NOT NULL");
- f1.checkString("regexp_replace('abcdefghijabc', 'abc(.)', '$1xy')",
+ f.checkString("regexp_replace('abcdefghijabc', 'abc(.)', '$1xy')",
"$1xyefghijabc", "VARCHAR NOT NULL");
- f1.checkString("regexp_replace('abc123', 'b(.*)(\\d)', '\\\\\\\\$
$\\\\\\\\')",
+ f.checkString("regexp_replace('abc123', 'b(.*)(\\d)', '\\\\\\\\$
$\\\\\\\\')",
"a\\$ $\\", "VARCHAR NOT NULL");
- // Tests to verify double-backslashes are ignored for indexing in other
dialects
- final SqlOperatorFixture f2 =
-
f0.withLibrary(SqlLibrary.MYSQL).withConformance(SqlConformanceEnum.MYSQL_5);
- f2.checkString("regexp_replace('abc16', 'b(.*)(\\d)', '\\\\2\\\\1X')",
"a\\2\\1X",
+ f.checkQuery("select regexp_replace('a b c', 'b', 'X')");
+ }
+
+ @Test void testRegexpReplacePg3Func() {
+ final SqlOperatorFixture f =
fixture().setFor(SqlLibraryOperators.REGEXP_REPLACE_PG_3)
+ .withLibrary(SqlLibrary.POSTGRESQL);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('a b c', 'b', 'X')", "a X c",
"VARCHAR NOT NULL");
- f2.checkString("regexp_replace('abcdefghijabc', 'abc(.)', '\\\\-11x')",
"\\-11xefghijabc",
+ f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X')", "X def ghi",
"VARCHAR NOT NULL");
- f2.checkString("regexp_replace('abcdefghijabc', 'abc(.)', '$1x')",
"dxefghijabc",
+ f.checkString("regexp_replace('100-200', '(\\d+)', 'num')", "num-200",
"VARCHAR NOT NULL");
+ f.checkString("regexp_replace('100-200', '(-)', '###')", "100###200",
+ "VARCHAR NOT NULL");
+ f.checkNull("regexp_replace(cast(null as varchar), '(-)', '###')");
+ f.checkNull("regexp_replace('100-200', cast(null as varchar), '###')");
+ f.checkNull("regexp_replace('100-200', '(-)', cast(null as varchar))");
+ f.checkString("regexp_replace('abc\t\ndef\t\nghi', '\t', '+')",
"abc+\ndef\t\nghi",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('abc\t\ndef\t\nghi', '\t\n', '+')",
"abc+def\t\nghi",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('abc\t\ndef\t\nghi', '\\w+', '+')",
"+\t\ndef\t\nghi",
+ "VARCHAR NOT NULL");
+
+ f.checkQuery("select regexp_replace('a b c', 'b', 'X')");
+ }
+
+ @Test void testRegexpReplacePg4Func() {
+ final SqlOperatorFixture f =
fixture().setFor(SqlLibraryOperators.REGEXP_REPLACE_PG_4)
+ .withLibrary(SqlLibrary.POSTGRESQL);
+
+ // Tests for regexp replace generic functionality
+ f.checkString("regexp_replace('abc def GHI', '[a-z]+', 'X', 'c')", "X def
GHI",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('ABC def GHI', '[a-z]+', 'X', 'i')", "X def
GHI",
+ "VARCHAR NOT NULL");
+ f.checkString("regexp_replace('abc def ghi', '[a-z]+', 'X', 'g')", "X X X",
+ "VARCHAR NOT NULL");
+
+ f.checkQuery("select regexp_replace('a b c', 'b', 'X', 'i')");
}
@Test void testRegexpExtractFunc() {
