cw_regexp_replace_generic.sqlx

config { hasOutput: true }
/*
 * Copyright 2022 Google LLC
 *
 * Licensed 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.
 */

 /* Generic regexp_replace, which is the 6-args version with regexp_mode already decoded
  *
  * haystack = the string to search
  * regexp = the regular expression
  * replacement = the string to replace the match with
  * offset = the 1-based offset where the search will begin
  * occurrence = the 1-based occurrence of the match to replace - overrides
  *   the 'g' flag
  * mode = the javascript regular expression mode flags
  */
CREATE OR REPLACE FUNCTION ${self()}(haystack STRING, regexp STRING, replacement STRING, offset INT64, occurrence INT64, mode STRING) RETURNS STRING
LANGUAGE js AS """
  if (haystack == null || regexp == null || replacement == null || offset == null || occurrence == null || mode == null) return null;
  replacement = replacement.replace('\\\\', '$');
  if (mode.indexOf('g') == -1) {
    mode = mode + 'g';
  }
  let regExp = new RegExp(regexp, mode);
  const relevantString = haystack.substring(offset - 1);
  const prefix = haystack.substr(0, haystack.length - relevantString.length);
  if (occurrence == 0) {
    return prefix + relevantString.replace(regExp, replacement);
  }
  for (let count = 0; count < occurrence - 1; count++) {
    if (regExp.exec(relevantString) === null) {
      break;
    }
  }
  const skipped = prefix + relevantString.substring(0, regExp.lastIndex);
  const remaining = relevantString.substring(regExp.lastIndex);
  regExp = new RegExp(regexp, mode.replace('g', ''));
  return skipped + remaining.replace(regExp, replacement);
""";