String and regular expression replace

[jasonkres]

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);
}

[nblumhardt]

Thanks for this suggestion @jasonkres; we're not expecting it will make it into 2022.1 or .2, but we're keen to continue the thread so will move it to a discussion.

[CrossBound]

I'm needing replace functionality and was disappointed to see Seq doesn't have anything like it right now. To my knowledge there's no workaround either since there's not even a basic case statement or anything like that. Please reconsider adding this feature.

[nblumhardt]

Thanks for the nudge @CrossBound! Pleasantly, 2023.1 nearly halved the work we'd need to do to implement this so I think it should be on the cards.

Is the basic replace(s, 'a', 'b') version sufficient for your usage? If so, and there's always exactly only one replacement to make per string, a concoction of concat, substring, and indexof can possibly achieve it as a workaround.

Not knowing the particulars of the scenario I can't picture how case would fit into it, but chained if/then/else can usually cover case/when constructs if it helps.

[nblumhardt]

We'll include this in the next major version. I've moved this to a new ticket: #2236 - thanks @jasonkres! 😎