Replace multiple characters from a string without using nested replacement functions

Question

I have an equation stored in my table. I collect one equation at a time and want to replace all the operators with some other symbol.

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

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

Spare character: ~

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

I tried the following query using Nested REPLACE functions and got the desired result:

 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 operators without using the nested replacement functions?

Answer 1

I created a SPLIT function to implement this because I need to perform this operation several times in PROCEDURE

DIFFERENCE FUNCTION

 create function [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end 

Code used in the procedure:

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

OUTPUT

 ~N_100~~6858~~~6858~~N_100~0_2~~N_35 

Answer 2

I find it easier and more understandable if you use a table for 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 3

There is no equivalent for Oracle TRANSLATE function in SQL Server, you need to use nested replacement 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 understand why you prefer this over nested REPLACE calls.

Answer 4

The easiest way is to use the TRANSLATE function. It is available from SQL Server 2017 (aka vNext) and higher.

TRANSLATE

Returns the string provided as the first argument after some of the characters specified in the second argument are translated into the target character set.

 TRANSLATE ( inputString, characters, translations) 

Returns a character expression of the same type as inputString, where the characters from the second argument are replaced by the corresponding characters from the third argument.

In your case:

 SELECT TRANSLATE('(N_100-(6858)*(6858)*N_100/0_2)%N_35', '+-*/%()','~~~~~~~') 

DBFiddle Demo