4th parameter Flags for REGEX(), tdf#113977

REGEX( Text ; Expression [ ; [ Replacement ] [ ; Flags ] ] )

REGEX(Text;Expression) extracts the first match of Expression in
Text. If there is no match, #N/A is returned.

REGEX(Text;Expression;Replacement) replaces the first match of
Expression in Text, not extracted. If there is no match, Text is
returned unmodified.

REGEX(Text;Expression;Replacement;"g") replaces all matches of
Expression in Text, not extracted. If there is no match, Text is
returned unmodified.

Change-Id: I9d26a48f40c64a2704d9d07576c8b1b98b2c7b84
Reviewed-on: https://gerrit.libreoffice.org/62545
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins

3 files changed, 42 insertions, 7 deletions

diff --git a/sc/inc/scfuncs.hrc b/sc/inc/scfuncs.hrc
index 5ed0949ca3b8..90a07857b33b 100644
--- a/sc/inc/scfuncs.hrc
+++ b/sc/inc/scfuncs.hrc
@@ -3819,13 +3819,15 @@ const char* SC_OPCODE_SUBSTITUTE_ARY[] =
 // -=*# Resource for function REGEX #*=-
 const char* SC_OPCODE_REGEX_ARY[] =
 {
-    NC_("SC_OPCODE_REGEX", "Matches and optionally replaces text using regular expressions."),
+    NC_("SC_OPCODE_REGEX", "Matches and extracts or optionally replaces text using regular expressions."),
     NC_("SC_OPCODE_REGEX", "Text"),
     NC_("SC_OPCODE_REGEX", "The text to be operated on."),
     NC_("SC_OPCODE_REGEX", "Expression"),
-    NC_("SC_OPCODE_REGEX", "The regular expression to be matched."),
+    NC_("SC_OPCODE_REGEX", "The regular expression pattern to be matched."),
     NC_("SC_OPCODE_REGEX", "Replacement"),
-    NC_("SC_OPCODE_REGEX", "The replacement text and expression.")
+    NC_("SC_OPCODE_REGEX", "The replacement text and references to capture groups."),
+    NC_("SC_OPCODE_REGEX", "Flags"),
+    NC_("SC_OPCODE_REGEX", "Text specifying option flags, \"g\" for global replacement.")
 };
 
 // -=*# Resource for function BASE #*=-
diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index 5c530b3c6009..5caf7a558344 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -808,7 +808,7 @@ ScFunctionList::ScFunctionList()
         { SC_OPCODE_REPLACEB, ENTRY(SC_OPCODE_REPLACEB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REPLACEB, 4, { 0, 0, 0, 0 } },
         { SC_OPCODE_FINDB, ENTRY(SC_OPCODE_FINDB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_FINDB, 3, { 0, 0, 1 } },
         { SC_OPCODE_SEARCHB, ENTRY(SC_OPCODE_SEARCHB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_SEARCHB, 3, { 0, 0, 1 } },
-        { SC_OPCODE_REGEX, ENTRY(SC_OPCODE_REGEX_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REGEX, 3, { 0, 0, 1 } }
+        { SC_OPCODE_REGEX, ENTRY(SC_OPCODE_REGEX_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REGEX, 4, { 0, 0, 1, 1 } }
     };
 
     ScFuncDesc* pDesc = nullptr;
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 3123335ea3b9..38e56deb52f7 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -9218,11 +9218,38 @@ void ScInterpreter::ScSearch()
 void ScInterpreter::ScRegex()
 {
     sal_uInt8 nParamCount = GetByte();
-    if (MustHaveParamCount( nParamCount, 2, 3))
+    if (MustHaveParamCount( nParamCount, 2, 4))
     {
+        // Flags are supported only for replacement, search match flags can be
+        // individually and much more flexible set in the regular expression
+        // pattern using (?ismwx-ismwx)
+        bool bGlobalReplacement = false;
+        if (nParamCount == 4)
+        {
+            // Empty flags string is valid => no flag set.
+            OUString aFlags( GetString().getString());
+            if (aFlags.getLength() > 1)
+            {
+                // Only one flag supported.
+                PushIllegalArgument();
+                return;
+            }
+            if (aFlags.getLength() == 1)
+            {
+                if (aFlags.indexOf('g') >= 0)
+                    bGlobalReplacement = true;
+                else
+                {
+                    // Unsupported flag.
+                    PushIllegalArgument();
+                    return;
+                }
+            }
+        }
+
         bool bReplacement = false;
         OUString aReplacement;
-        if (nParamCount == 3)
+        if (nParamCount >= 3)
         {
             // A missing argument is not an empty string to replace the match.
             if (IsMissing())
@@ -9233,6 +9260,8 @@ void ScInterpreter::ScRegex()
                 bReplacement = true;
             }
         }
+        // If bGlobalReplacement==true and bReplacement==false then
+        // bGlobalReplacement is silently ignored.
 
         OUString aExpression = GetString().getString();
         OUString aText = GetString().getString();
@@ -9284,7 +9313,11 @@ void ScInterpreter::ScRegex()
         // Replace first occurrence of match with replacement.
         const icu::UnicodeString aIcuReplacement(
                 reinterpret_cast<const UChar*>(aReplacement.getStr()), aReplacement.getLength());
-        icu::UnicodeString aReplaced( aRegexMatcher.replaceFirst( aIcuReplacement, status));
+        icu::UnicodeString aReplaced;
+        if (bGlobalReplacement)
+            aReplaced = aRegexMatcher.replaceAll( aIcuReplacement, status);
+        else
+            aReplaced = aRegexMatcher.replaceFirst( aIcuReplacement, status);
         if (U_FAILURE(status))
         {
             // Some error, e.g. extraneous $1 without group.