Sql-server – SQL Server: Replace with wildcards

pattern matchingreplacesql servert-sql

Does Microsoft SQL Server natively support some sort of replace function using wild cards? I gather that Regular Expressions are not available natively.

I note that there is a PATINDEX function which can be used to bolt together a solution — is there one something which does it more simply?

For example REPLACE(data,'[xy]','q') to replace x or y with q.

Best Answer

The REPLACE built-in function does not support patterns or wildcards; only LIKE and PATINDEX do.

Assuming that you really just want the simple single-character replacement as shown in the question, then you can call REPLACE twice, one nested in the other, as follows:

SELECT REPLACE(
               REPLACE('A B x 3 y Z x 943 yy!',
                       'x',
                       'q'),
               'y',
               'q');

Returns:

A B q 3 q Z q 943 qq!

If you do need more complex pattern matching / replacement, then you will need to do that via Regular Expressions, which can only be done via SQLCLR. Several SQLCLR RegEx functions are available in the Free version of the SQL# SQLCLR library (which I wrote), one of them being RegEx_Replace[4k]() (the 4k version is for when you are certain that you will never need more than 4000 characters, hence you can get better performance from not using NVARCHAR(MAX) as an input parameter or return value).

The equivalent of the two nested REPLACE calls would be done as follows (and using the pattern syntax shown in the question):

SELECT SQL#.RegEx_Replace4k(N'A B x 3 y Z x 943 yy!', N'[xy]', N'q', -1, 1, NULL);

Returns:

A B q 3 q Z q 943 qq!

But, since we are talking about the possibility of a more complex pattern, one that cannot be done easily in T-SQL, we can use a quantifier on the pattern to have it replace any number of contiguous x or y characters with a single q:

SELECT SQL#.RegEx_Replace4k(N'A B x 3 y Z xx 943 yyxxyxy!', N'[xy]+', N'q', -1, 1, NULL);

Returns:

A B q 3 q Z q 943 q!

Please note that the input string was changed slightly from the previous two examples to add an extra x after the Z, and to add an extra xxyxy to the yy at the end. And, in both cases, the multi-character fragment was replaced with a single q.

For more info on working with strings and collations, please visit: Collations Info

Related Question