6

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.

  • Sounds overly simplistic - are you really replacing single characters with something else? (I ask because anything more complex would be hard to represent inside a single string - no regex natively but you may want to look into one of the many solutions using CLR.) – Aaron Bertrand Feb 1 '17 at 4:24
  • 3
    I wonder if this is a XY problem. – vonPryz Feb 1 '17 at 12:59
3

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

1

This post: https://stackoverflow.com/questions/13253259/sql-server-replace-command-with-wildcard seems fairly similar and references this link: https://stackoverflow.com/questions/150977/perform-regex-replace-in-an-sql-query

Another option is something like

 UPDATE myTable
 SET myField = LEFT(myField, PATINDEX('%Z%', myField))
 WHERE myField LIKE '%X%'
 OR  myField LIKE '%Y%'
  • Note that your sample code will not replace anything; It would drop anything to the right of the first 'Z' in each field, as long as the field also contains an 'X' or 'Y', which doesn't appear to be what the OP had in mind. – RDFozz Jan 3 at 21:32
  • While it does not answer the posters question, this actually helped me in a situation where I had to standardize export paths (where the export path could be D:\mypath E:\mypath or 10.10.10.128\C$\mypath) See my answer below – Richard Varno Apr 24 at 13:50
0

The above answer from CaM while it does not answer the posters question, did help me in a situation where I had to standardize export paths (where the export path could be D:\mypath E:\mypath or 10.10.10.128\C$\mypath etc...) maybe it will help someone else

-- standardize output paths
UPDATE dbo.ELDPdf
 SET OutputPath = SUBSTRING(OutputPath, PATINDEX('%$%', OutputPath)+2,1000)
 WHERE JobSummaryId = @JobSummaryId
 AND OutputPath LIKE '%$%'

UPDATE dbo.ELDPdf
 SET OutputPath = SUBSTRING(OutputPath, PATINDEX('%:%', OutputPath)+2,1000)
 WHERE JobSummaryId = @JobSummaryId
 AND OutputPath LIKE '%:\%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.