How to Replace Multiple Characters in SQL

SQL Replace multiple different characters in string

You just need to daisy-chain them:

REPLACE(REPLACE(T2.[ShipToCode], '&', 'and'), ',', '')

How to Replace Multiple Characters in SQL?

I would seriously consider making a CLR UDF instead and using regular expressions (both the string and the pattern can be passed in as parameters) to do a complete search and replace for a range of characters. It should easily outperform this SQL UDF.

replace multiple values at the same time - in order to convert a string to a number

I can think of two approaches.

The first is to use a bunch of nested replace() statements:

select replace(replace(replace(col, '$', ''), '£', ''), 'n/a', '')

and so on.

The second is to find the first digit and try converting from there. This requires complicated logic with patindex(). Here is an example:

select cast(left(substring(col, patindex('%[0-9]%', col), 1000),
                 patindex('%[^0-9]%', substring(col, patindex('%[0-9]%', col), 1000)) - 1
                ) as int)

Replace multiple characters in SQL

The below will work (even it's not a smart solution).

select 
    table_value, 
    replace(replace(replace(replace(table_value, 'M', 'MXXTING'), 'E', 'XMAIL'), 'P', 'PHONX'), 'X', 'E') required_value 
from foobar

How to replace multiple characters with a specific character in mysql with regexp_replace?

Try this

SELECT  regexp_replace(name, '[^a-zA-Z0-9_]', '_') 

db<>fiddle

presto replace multiple characters

You can use regexp_replace to remove multiple characters:

presto> WITH my_table(id, a_string) AS (
     ->      VALUES
     ->          (1, 'test{' || CHR(39) || '}xyz'),
     ->          (2, 'x'),
     ->          (3, '{y}'),
     ->          (1, 'z'),
     ->          (2, 'none'),
     ->          (3, 'none' || CHR(39) || 'xyz')
     -> )
     -> SELECT regexp_replace(a_string, '[{}'']') FROM my_table;
  _col0
---------
 testxyz
 x
 y
 z
 none
 nonexyz
(6 rows)

Replace multiple characters from string without using any nested replace functions

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

---

Related Topics