SQL Server replacing parts of a string using regex

Question

This should be a simple one, though I didn't quite find a suitable solution.

I need to implement a (rather) simple replacement using SQL (SQL Server) as follows. Imagine you have a string that looks like:

'This is a simple example where aaa[0004] should become aaa[4] and b[0],c[1] should remain unchanged'

In other words, a pattern [0004] should become [4].

I initially thought of making the replacing like:

  SET @MyString = REPLACE(@MyString,'[0','[') ;

but, even before testing it, I realized that it would also convert [0] to [], which is not want I want.

I know how to do it quite simply in PL/SQL, but in SQL Server I'm having difficulties.

If you are allowed to use CLR assemblies, SqlSharp has implementations of RegEx along with a whole host of great CLR features. — SqlZim
– SqlZim, Commented Feb 12, 2017 at 14:19
Thank you @SqlZim, but no, I cannot use that type of things (company policy). I thought of searching for [0, from there ], and if there are digits in-between, replace the first [0 with [. Then loop till no more such cases were found. That would work, but its ugliness causes me pain. — FDavidov
– FDavidov, Commented Feb 12, 2017 at 14:22

John Cappelletti · Accepted Answer · 2017-02-13 13:04:53Z

2

Another option. This will replace up to 25 occurrences of '[0' and multiple [...]'s per line.

Example

Declare @MyString varchar(max) = 'This [0] [000] is a simple example where aaa[0004] should become aaa[4] and test [0]'

Select @MyString = Replace(@MyString,MapFrom,MapTo)
  From (
        Select MapFrom='0]',MapTo='§§0]'
        Union All           
        Select Top 25 '[0','['  From master..spt_values 
        Union All
        Select '§§0]','0]'
        ) b 

Select @MyString

Returns

This [0] [0] is a simple example where aaa[4] should become aaa[4] and test [0]

Note: If processing a table, it will be a small matter to apply some XML

edited Feb 13, 2017 at 13:04

answered Feb 12, 2017 at 14:41

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SqlZim Over a year ago

Nicely done. Note: this also works with the updated test string: rextester.com/VPYA60373 returning This is a simple example where aaa[4] should become aaa[4] and b[0],c[1] should remain unchanged

John Cappelletti Over a year ago

@SqlZim Thanks, sometimes you just need a hammer. It's not elegant but gets the job done :)

SqlZim Over a year ago

You must have some fancy hammers!

FDavidov Over a year ago

@JohnCappelletti, indeed, great solution that yields ALMOST ALWAYS the correct result. Why ALMOST ALWAYS? Try it with: 'This is a sample string with [] and aaa[0004]'. The [] is replaced with [0] while it should remain unchanged. If you fix this, I'll certainly mark it as THE solution. Thanks!!!!

FDavidov Over a year ago

@JohnCappelletti, I took Martin's suggestion as a seed and worked a similar one (with no limitation on the number of leading 0). Will be posting the solution I adopted later today. Thanks for the very interesting suggestion and sorry for not marking it as THE solution (though up-voted it for its originality).

|

Martin Smith · Accepted Answer · 2017-02-12 18:49:13Z

1

The following would work for up to 15 leading zeroes if there is a character (~ below) that you can be reasonably confident will never appear in the data.

SELECT 
 REPLACE(
  REPLACE(
   REPLACE(
    REPLACE(
     REPLACE(
       REPLACE(X,'0]', '~'), 
     '[00000000','['),
    '[0000','['),
   '[00','['),
  '[0','['),
 '~','0]')
FROM YourTable

Demo

edited Feb 12, 2017 at 18:49

answered Feb 12, 2017 at 18:40

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

3 Comments

FDavidov Over a year ago

Thank you Martin for your suggestion. I took it as a seed and worked out another solution with no limitation on the number of 0's. Will post it later today in case you are interested. Cheers!

Martin Smith Over a year ago

@FDavidov - Is there in practice no limitation on the number of 0s? What kind of number is this? 15 is more than enough for a 32 bit int and you can increase the number of leading zerroes supported to 31 by adding another REPLACE with 16 zeroes. Procedural looping should generally be avoided in SQL Server.

FDavidov Over a year ago

Martin, I fully agree with you regarding the actual need for anything beyond 15 zeros. In practice, I don't expect more than 1-2 leading zeros. My approach (in general) is to device mechanisms that don't have a limitation at all (even when in practice a limitless condition would never happen) if the solution is not costly. I do not agree with you regarding your suggestion of avoiding loops within SQL. They should be used when it is correct to use them, and avoid them when it is not. SQL is a programming language like any other, and should be used smartly, like any other.

FDavidov · Accepted Answer · 2017-02-13 06:51:44Z

Thanks to Martin's suggestion, I worked the following solution:

    Declare @MyString   varchar(max) ;
    Declare @MyString_T varchar(max) ;

    SET @MyString = 'This is a simple example where aaa[00000000000000000000000000004] should become aaa[40] and test [000] and ccc[]' ;

    WHILE (1=1)
        BEGIN
            SET @MyString_T = REPLACE(
                                REPLACE(
                                  REPLACE(@MyString,'0]', '~'), 
                                '[0','['),
                              '~','0]') ;

            IF @MyString_T = @MyString
                BREAK ;
            ELSE
               SET @MyString = @MyString_T ;
        END ;

    Print(@MyString) ;

and the result is:

This is a simple example where aaa[4] should become aaa[40] and test [0] and ccc[]

Thanks again to Martin!!!

Collectives™ on Stack Overflow

SQL Server replacing parts of a string using regex

3 Answers 3

7 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related