Regex pattern inside SQL Replace function

Question

SELECT REPLACE   lt strong gt 100 lt  strong gt  lt b gt  00 GB        -  d    0 1  d              I want to replace any markup between two parts of the number with above regex  but it does not seem to work  I m not sure if it is regex syntax that s wrong because I tried simpler one such as     0-9    just to test but it didn t work either  Does anyone know how can I achieve this

User · Answer

Here is a function I wrote to accomplish this based off of the previous answers.

CREATE FUNCTION dbo.RepetitiveReplace
(
    @P_String VARCHAR(MAX),
    @P_Pattern VARCHAR(MAX),
    @P_ReplaceString VARCHAR(MAX),
    @P_ReplaceLength INT = 1
)
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @Index INT;

    -- Get starting point of pattern
    SET @Index = PATINDEX(@P_Pattern, @P_String);

    while @Index > 0
    begin
        --replace matching charactger at index
        SET @P_String = STUFF(@P_String, PATINDEX(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString);
        SET @Index = PATINDEX(@P_Pattern, @P_String);
    end

    RETURN @P_String;
END;

Gist

Edit:

Originally I had a recursive function here which does not play well with sql server as it has a 32 nesting level limit which would result in an error like the below any time you attempt to make 32+ replacements with the function. Instead of trying to make a server level change to allow more nesting (which could be dangerous like allow never ending loops) switching to a while loop makes a lot more sense.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

User · Answer

For those looking for a performant and easy solution and are willing to enable CLR  create database TestSQLFunctions go use TestSQLFunctions go alter database TestSQLFunctions set trustworthy on  EXEC sp configure  clr enabled   1 RECONFIGURE WITH OVERRIDE go  CREATE ASSEMBLY  SQLFunctions  AUTHORIZATION  dbo  FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004BE8B85F0000000000000000E00022200B013000000800000006000000000000C2270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000702700004F000000004000009803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C8070000002000000008000000020000000000000000000000000000200000602E72737263000000980300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000A4270000000000004800000002000500682000000807000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003A022D02022A020304281000000A2A1E02281100000A2A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000018020000237E000084020000CC02000023537472696E6773000000005005000004000000235553005405000010000000234755494400000064050000A401000023426C6F620000000000000002000001471500000900000000FA0133001600000100000013000000020000000200000003000000110000000F00000001000000030000000000CA01010000000000060025014F02060092014F02060044001D020F006F02000006006C00E20106000801E2010600D400E20106007901E20106004501E20106005E01E20106008300E2010600580030020600360030020600B700E20106009E00B0010600AA02DB010A00F300FC010A001F00FC010E00C3027E02000000000100000000000100010001001000C3029D0241000100010050200000000096002E001A0001005F2000000000861817020600040000000100BD0200000200F40100000300B102090017020100110017020600190017020A0029001702100031001702100039001702100041001702100049001702100051001702100059001702100061001702150069001702100071001702100079001702100089001702060099002E001A0081001702060020007B0010012E000B002A002E00130033002E001B0052002E0023005B002E002B006D002E0033006D002E003B006D002E0043005B002E004B0073002E0053006D002E005B006D002E0063008B002E006B00B5002E007300C2000480000001000000000000000000000000009D020000040000000000000000000000210016000000000004000000000000000000000021000A00000000000400000000000000000000002100DB010000000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200446174614163636573734B696E64005265706C61636500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E56657273696F6E696E670053514C46756E6374696F6E732E646C6C0053797374656D0053797374656D2E5265666C656374696F6E007061747465726E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E546578742E526567756C617245787072657373696F6E730053514C46756E6374696F6E73004F626A656374007265706C6163656D656E7400696E70757400526567657800000000000000003A1617E607071B47B964858BCD87458B00042001010803200001052001011111042001010E04200101020600030E0E0E0E08B77A5C561934E0890801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001101000C53514C46756E6374696F6E73000005010000000017010012436F7079726967687420C2A920203230323000002901002434346436386231632D393735312D343938612D396665352D32316666333934303738303900000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E352E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E352E32808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000000000982700000000000000000000B2270000002000000000000000000000000000000000000000000000A4270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000003C03000000000000000000003C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0049C020000010053007400720069006E006700460069006C00650049006E0066006F0000007802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000042000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C00460075006E006300740069006F006E00730000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000042001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C00460075006E006300740069006F006E0073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003200300000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000004A00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C00460075006E006300740069006F006E0073002E0064006C006C00000000003A000D000100500072006F0064007500630074004E0061006D00650000000000530051004C00460075006E006300740069006F006E00730000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C43700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION SET   SAFE  go  CREATE FUNCTION RegexReplace       input nvarchar max        pattern nvarchar max        replacement nvarchar max    RETURNS nvarchar   max  AS EXTERNAL NAME SQLFunctions  SQLFunctions Regex  Replace    go  -- outputs This is a test  select dbo RegexReplace  This is a test 12345    0-9        Content of the DLL

User · Answer

You can use PATINDEX to find the first index of the pattern  string s  occurrence  Then use STUFF to stuff another string into the pattern string  matched   Loop through each row  Replace each illegal characters with what you want  In your case replace non numeric with blank  The inner loop is if you have more than one illegal character in a current cell that of the loop   DECLARE  counter int  SET  counter   0  WHILE  counter  lt   SELECT MAX ID COLUMN  FROM Table   BEGIN        WHILE 1   1     BEGIN         DECLARE  RetVal varchar 50           SET  RetVal     SELECT Column   STUFF Column  PATINDEX     0-9      Column  1              FROM Table         WHERE ID COLUMN    counter           IF  RetVal IS NOT NULL                   UPDATE Table SET           Column    RetVal           WHERE ID COLUMN    counter         ELSE             break     END      SET  counter    counter   1 END   Caution  This is slow though  Having a varchar column may impact  So using LTRIM RTRIM may help a bit  Regardless  it is slow   Credit goes to this StackOverFlow answer   EDIT Credit also goes to  srutzky  Edit  by  Tmdean  Instead of doing one row at a time  this answer can be adapted to a more set-based solution  It still iterates the max of the number of non-numeric characters in a single row  so it s not ideal  but I think it should be acceptable in most situations   WHILE 1   1 BEGIN     WITH q AS          SELECT ID Column  PATINDEX     0-9      Column  AS n         FROM Table      UPDATE Table     SET Column   STUFF Column  q n  1          FROM q     WHERE Table ID Column   q ID Column AND q n    0       IF   ROWCOUNT   0 BREAK  END    You can also improve efficiency quite a lot if you maintain a bit column in the table that indicates whether the field has been scrubbed yet   NULL represents  Unknown  in my example and should be the column default    DECLARE  done bit   0  WHILE  done   0 BEGIN     WITH q AS          SELECT ID Column  PATINDEX     0-9      Column  AS n         FROM Table         WHERE COALESCE Scrubbed Column  0    0      UPDATE Table     SET Column   STUFF Column  q n  1               Scrubbed Column   0     FROM q     WHERE Table ID Column   q ID Column AND q n    0       IF   ROWCOUNT   0 SET  done   1       -- if Scrubbed Column is still NULL  then the PATINDEX     -- must have given 0     UPDATE table     SET Scrubbed Column   CASE         WHEN Scrubbed Column IS NULL THEN 1         ELSE NULLIF Scrubbed Column  0      END  END    If you don t want to change your schema  this is easy to adapt to store intermediate results in a table valued variable which gets applied to the actual table at the end

User · Answer

Instead of stripping out the found character by its sole position  using Replace Column  BadFoundCharacter      could be substantially faster  Additionally  instead of just replacing the one bad character found next in each column  this replaces all those found   WHILE 1   1 BEGIN     UPDATE dbo YourTable     SET Column   Replace Column  Substring Column  PatIndex     0-9 -     Column   1           WHERE Column LIKE     0-9 -        If   RowCount   0 BREAK  END    I am convinced this will work better than the accepted answer  if only because it does fewer operations  There are other ways that might also be faster  but I don t have time to explore those right now

User · Answer

I stumbled across this post looking for something else but thought I d mention a solution I use which is far more efficient - and really should be the default implementation of any function when used with a set based query - which is to use a cross applied table function   Seems the topic is still active so hopefully this is useful to someone   Example runtime on a few of the answers so far based on running recursive set based queries or scalar function  based on 1m rows test set removing the chars from a random newid  ranges from 34s to 2m05s for the WHILE loop examples and from 1m3s to  forever  for the function examples   Using a table function with cross apply achieves the same goal in 10s   You may need to adjust it to suit your needs such as the max length it handles   Function   CREATE FUNCTION  dbo   RemoveChars   InputUnit VARCHAR 40   RETURNS TABLE AS RETURN               WITH Numbers prep Number  AS                               SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1                        Numbers Number  AS                               SELECT TOP  ISNULL LEN  InputUnit  0                       row number   OVER  ORDER BY  SELECT NULL                   FROM Numbers prep a                     CROSS JOIN Numbers prep b                       SELECT             OutputUnit         FROM                               SELECT                     substring  InputUnit Number 1                  FROM  Numbers                 WHERE substring  InputUnit Number 1  like    0-9                    ORDER BY Number                 FOR XML PATH                   Sub OutputUnit          Usage   UPDATE t SET column   o OutputUnit FROM   t t CROSS APPLY  dbo   RemoveChars  t column  o

User · Answer

i think this solution is faster and simple  i use always CTE recursive beacuse WHILE is so slow on mssql  I use it in projects I work with and large databases     Function            dbo kSql ReplaceRegExp Create Date         20 02 2021 Author              Karcan Ozbal  Description         The given string value will be replaced according to the given regexp pattern   Parameter s          Value         Value Text to REPLACE                       RegExp        The regexp pattern to be used for REPLACE operation   Usage               select dbo kSql ReplaceRegExp  2T3EST5     0-9     Output               TEST     ALTER FUNCTION  dbo   kSql ReplaceRegExp        Value nvarchar max        RegExp nvarchar 50    RETURNS nvarchar max  AS BEGIN     DECLARE  Result nvarchar max        WITH CTE AS           SELECT NUM   1  VALUE    Value  IDX   PATINDEX  RegExp   Value          UNION ALL         SELECT NUM   1  VALUE   REPLACE VALUE  SUBSTRING VALUE IDX 1       IDX   PATINDEX  RegExp  REPLACE VALUE  SUBSTRING VALUE IDX 1                FROM CTE         WHERE IDX  gt  0           SELECT TOP 1   Result   VALUE      FROM CTE      ORDER BY NUM DESC     OPTION  maxrecursion 0       RETURN  Result END

User · Answer

I ve created this function to clean up a string that contained non numeric characters in a time field  The time contained question marks when they did not added the minutes  something like this 20     Function loops through each character and replaces the   with a 0     CREATE FUNCTION  dbo   CleanTime        -- Add the parameters for the function here      intime nvarchar 10     RETURNS nvarchar 5  AS BEGIN     -- Declare the return variable here     DECLARE  ResultVar nvarchar 5      DECLARE  char char 1      -- Add the T-SQL statements to compute the return value here     DECLARE  i int   1     WHILE  i  lt   LEN  intime      BEGIN     SELECT  char    CASE WHEN substring  intime  i 1  like    0-9     THEN substring  intime  i 1  ELSE  0  END     SELECT  ResultVar   concat  ResultVar  char         set  i     i   1            END      -- Return the result of the function     RETURN  ResultVar  END

User · Answer

I think a simpler and faster approach is iterate by each character of the alphabet   DECLARE  i int SET  i   0  WHILE  i  lt  256  BEGIN        IF char  i  NOT IN   0    1    2    3    4    5    6    7    8    9                     UPDATE Table SET Column   replace Column  char  i            SET  i    i   1  END

User · Answer

Wrapping the solution inside a SQL function could be useful if you want to reuse it  I m even doing it at the cell level  that s why I m putting this as a different answer   CREATE FUNCTION  dbo   fnReplaceInvalidChars    string VARCHAR 300   RETURNS VARCHAR 300  BEGIN     DECLARE  str VARCHAR 300     string      DECLARE  Pattern VARCHAR  20        a-zA-Z0-9         DECLARE  Len INT      SELECT  Len   LEN  String        WHILE  Len  gt  0      BEGIN         SET  Len    Len - 1          IF  PATINDEX  Pattern  str   gt  0              BEGIN                 SELECT  str   STUFF  str  PATINDEX  Pattern  str  1                      END         ELSE         BEGIN             BREAK          END     END          RETURN  str END

User · Answer

In a general sense  SQL Server does not support regular expressions and you cannot use them in the native T-SQL code    You could write a CLR function to do that  See here  for example

User · Answer

If you are doing this just for a parameter coming into a Stored Procedure  you can use the following   declare  badIndex int set  badIndex   PatIndex     0-9      Param  while  badIndex  gt  0     set  Param   Replace  Param  Substring  Param   badIndex  1           set  badIndex   PatIndex     0-9      Param

[sql-server] Regex pattern inside SQL Replace function?

Edit:

Examples related to sql-server

Examples related to regex