CODEWITHSUNDEEP
sql-serverregex
FDavidov
FDavidov

Reputation: 3675

SQL Server replacing parts of a string using regex

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.

Upvotes: 0

Views: 74

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81950

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

Upvotes: 2

FDavidov
FDavidov

Reputation: 3675

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!!!

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453212

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

Upvotes: 1

Related Questions