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?

I have some other operators also and I have to write Replace function multiple times. So I am finding alternate way to do this. — Saharsh Shah
– Saharsh Shah, Commented Nov 7, 2013 at 12:14

Shane Gebs · Accepted Answer · 2014-12-18 16:18:28Z

12

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

answered Dec 18, 2014 at 16:18

Shane Gebs

1211 silver badge2 bronze badges

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

4 Comments

Solomon Rutzky Over a year ago

This is definitely better than using a split function (and a horribly inefficient one at that) to spit out a table dynamically. It is also 1000 times better than a while loop using a WHILE loop and PATINDEX :). +1

Code Jockey Over a year ago

@srutzky OR Shane Gebs, if you're out there -- I'd love to have something like this (running on a field from each row of a table variable rather than a scalar), but for some reason, it is only replacing the first pair of values (first row) of #Replace (?) ---- that is, I've got letter pairs ('a', '+a'), ('b','+b'),('c'...etc and it's replacing all occurrences of a in the field of each row with +a, but not the b or c, etc replacements (?)

Solomon Rutzky Over a year ago

@CodeJockey Create a new question, reference this answer, and show what your current code is and the data you are trying to manipulate. Then post a link to that question here and I will answer it.

Ayorus Over a year ago

@SolomonRutzky Hey ! I am trying to do exactly what CodeJockey asked for. Would you mind take a look at this question? stackoverflow.com/questions/45800289

Twinkles · Accepted Answer · 2013-11-07 12:59:59Z

5

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.

edited Nov 7, 2013 at 12:59

answered Nov 7, 2013 at 11:52

Twinkles

1,9941 gold badge17 silver badges31 bronze badges

1 Comment

Saharsh Shah Over a year ago

I want alternative to do this operation.

Lukasz Szozda · Accepted Answer · 2017-11-08 15:01:22Z

2

The easiest way is to use TRANSLATE function. It is availble from SQL Server 2017 (aka vNext) and above.

TRANSLATE

Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.
TRANSLATE ( inputString, characters, translations) 
Returns a character expression of the same type as inputString where characters from the second argument are replaced with the matching characters from third argument.

In your case:

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

DBFiddle Demo

edited Nov 8, 2017 at 15:01

answered Dec 28, 2016 at 17:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

Saharsh Shah · Accepted Answer · 2014-01-10 04:57:55Z

0

I had created a SPLIT function to implement this because I need to implement this operation multiple time in PROCEDURE

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

answered Jan 10, 2014 at 4:57

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

5 Comments

Solomon Rutzky Over a year ago

This is no need for a split function. The only purpose it is serving is to dynamically spit out a table. Since the replacement characters are a small, finite set, it is much much better to just create a temp table as shown in @Shane's answer. Also, there are many ways to accomplish a string split in T-SQL, and you have chosen the slowest. Please look into a SQLCLR-based splitter (you can get for free via SQL# (which I am the creator of but String_Split is in the Free version) or, at the very least, an XML-based splitter.

Saharsh Shah Over a year ago

@srutzky Thank for your comment but I have to apply some business login on splitted values so I had used SPLIT function to split the values. So every time I can't use temporary table.

Solomon Rutzky Over a year ago

Ok about using split vs temp table. But, I am also saying to not use that split function. Split functions based on WHILE loops are horribly inefficient. There are several better choices.

Saharsh Shah Over a year ago

@srutzky Can you provide me the other way to write split function

Solomon Rutzky Over a year ago

I provided a link in my first comment to a free SQLCLR splitter. Or you can search the web for examples if you want to write your own.

Tsahi Asher · Accepted Answer · 2019-11-21 12:29:20Z

0

you can replace all the operators without using nested replace functions

DECLARE @Pattern VARCHAR(300) ='%[+-\*/%()]%';
DECLARE @String VARCHAR(300) ='(N_100-(6858)*(6858)*N_100/0_2)%N_35';
WHILE PatIndex(@Pattern, @String) <> 0
    SELECT @String=Replace(@String, Substring(@String, PatIndex(@Pattern, @String), 1), '~')
SELECT @String

edited Nov 21, 2019 at 12:29

Tsahi Asher

1,83417 silver badges30 bronze badges

answered Nov 21, 2019 at 11:29

komal siddhapura

1

Collectives™ on Stack Overflow

Replace multiple characters from string without using any nested replace functions

5 Answers 5

4 Comments

1 Comment

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related