String and regular expression replace

[nblumhardt]

Originally posted by @jasonkres in https://github.com/datalust/seq-tickets/discussions/1454

Overview

Replace is an important string function. Many string manipulations are currently impossible without it.

Syntax: Replace(text, pattern, replacement)

NOTE: This is an SQL standard function for the case when pattern is a string.

• Perform typical string replace when pattern is a string.
• Perform regular expression global replace when pattern is a regular expression.
  • If the regular expression contains capturing groups, then $1, $2, etc. have special meaning in the replacement (as backreferences).

Examples

-- pattern is a string:
Replace('Hello World* World*', 'World*', 'WWorld**')               => 'Hello WWorld** WWorld**'
Replace('Hello World World', 'world', 'everyone')                  => 'Hello World World'       -- No matches (case sensitive)
Replace('Hello World World', 'world', 'everyone') ci               => 'Hello everyone everyone'
Replace('Hello World World', 'W', '')                              => 'Hello orld orld'
Replace('Hello World World', '', 'everyone')                       => 'Hello World World'       -- Pattern '' never matches (see Microsoft SQL Server)
Replace(null, '', 'everyone')                                      => null
Replace('Hello World World', '', null)                             => null
Replace(null, '', null)                                            => null

-- pattern is a regular expression:
Replace('Hello World', /l./, 'X')                                  => 'HeXo WorX'
Replace('Hello World', /[HW]/, '')                                 => 'ello orld'
Replace('Hello World', /(h.+) (w.+)/, 'First $1$1 Second $2$2')    => 'Hello World'             -- No matches (case sensitive)
Replace('Hello World', /(h.+) (w.+)/, 'First $1$1 Second $2$2') ci => 'HelloHello WorldWorld'
Replace(null, /l./, 'X')                                           => null
Replace(null, /l./, null)                                          => null

-- pattern is null:
Replace(null, null, null)                                          => null
Replace('A', null, null)                                           => null
Replace(null, null, 'B')                                           => null

Precise Definition

Various edge cases are resolved to be consistent with these .NET/C# functions. (Case-insensitive mode can be defined with the appropriate changes.)

string Replace(string text, Regex pattern, string replacement)
{
    if (text == null || pattern == null || replacement == null)
        return null;

    return pattern.Replace(text, replacement);
}

string Replace(string text, string pattern, string replacement)
{
    if (text == null || pattern == null || replacement == null)
        return null;

    if (pattern.Length == 0)
        return text; // Pattern '' never matches (see Microsoft SQL Server)

    return text.Replace(pattern, replacement);
}