Replace multiple characters from string without using any nested replace functions

Question

I have an equation stored in my table. I am fetching one equation at a time and want to replace all the operators with any other character.

Input String: (N_100-(6858)*(6858)*N_100/0_2)%N_35

Operators or patterns: (+, -, *, /, %, (, ))

Replacement character: ~

Output String: ~N_100~~6858~~~6858~~N_100~0_2~~N_35

I had tried below query with Nested REPLACE Functions and I got desired output:

DECLARE @NEWSTRING VARCHAR(100) 
SET @NEWSTRING = '(N_100-(6858)*(6858)*N_100/0_2)%N_35' ;
SELECT @NEWSTRING = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    @NEWSTRING, '+', '~'), '-', '~'), '*', '~'), '/', '~')
                   , '%', '~'), '(', '~'), ')', '~')
PRINT @NEWSTRING

Output: ~N_100~~6858~~~6858~~N_100~0_2~~N_35

How can I replace all the operators without using nested replace functions?

Answer 1

I believe it is easier and more readable if you use a table to drive this.

declare @String varchar(max) = '(N_100-(6858)*(6858)*N_100/0_2)%N_35'

--table containing values to be replaced
create table #Replace 
(
    StringToReplace varchar(100) not null primary key clustered
    ,ReplacementString varchar(100) not null    
)

insert into #Replace (StringToReplace, ReplacementString)
values ('+', '~')
    ,('-', '~')
    ,('*', '~')
    ,('/', '~')
    ,('%', '~')
    ,('(', '~')
    ,(')', '~')

select @String = replace(@String, StringToReplace, ReplacementString)
from #Replace a

select @String

drop table #Replace

Answer 2

There is not equivalent for the TRANSLATE function from Oracle in SQL Server, you have to use nested replace functions.

The following solution is technically correct:

DECLARE @newstring VARCHAR(100) = '(N_100-(6858)*(6858)*N_100/0_2)%N_35';
DECLARE @pattern VARCHAR(100) = '%[+-\*/%()]%';
DECLARE @i INT;
BEGIN
  SET @i = PATINDEX(@pattern,@newstring) 
  WHILE @i <> 0
  BEGIN
    SET @newstring = LEFT(@newstring,@i-1) + '~' + SUBSTRING(@newstring,@i+1,100);
    SET @i = PATINDEX(@pattern,@newstring) 
  END
  SELECT @newstring;
END;

But I do not see why you would favor this over nested REPLACE calls.