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 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 strings in SQL?

The code you posted will work, it just won't give correct results.

take this example data:

create table #foobar (table_value varchar (100))

Insert into #foobar
values ('M,E'), ('M,E,P'), ('E'), ('M,P')
Insert into #foobar
values ('P,E')

When you run:

select table_value, 
   replace(replace(replace(table_value, 'M', 'MEETING'), 'E', 'EMAIL'), 'P', 'PHONE') required_value 
from #foobar

You get these results

table_value required_value
M,E          MEMAILEMAILTING,EMAIL
M,E,P        MEMAILEMAILTING,EMAIL,PHONE
E            EMAIL
M,P          MEMAILEMAILTING,PHONE
P,E          PHONE,EMAIL

As you can see the problem is that in replacing m with meeting, you now have two new 'E' values in the text that will in turn get replaced with email.

So if you want to fix this the first and best solution is to stop storing data this way. Comma delimited lists in a field are almost always a bad idea. It violates the first normal form. You should have a related table where each type in the list is a separate record attached to the same id value from the original table. This is database 101.

However at times, you may be stuck with this, that's why the link wants to split the data out into a table and then process from there, then join it back into one field after updating if you need to retain the current structure and aren't just processing data in a staging table. Or alternatively not use values that will mingle as these do.

Replacing multiple special characters in oracle

As per the regular expression operators and metasymbols documentation:

Put ] as the first character of the (negated) character group;
- as the last; and
Do not put . immediately after [ or it can be matched as the start of a coalition element [..] if there is a second . later in the expression.

Also:

Double up the single quote (to escape it, so it does not terminate the string literal); and
Include the non-special characters a-zA-Z0-9 in the capture group too otherwise they will be matched.

Which gives you the regular expression:

SELECT emp_address,
       REGEXP_REPLACE(
         emp_address,
         '^[^][,.$''\*&!%^{}?a-zA-Z0-9-]|[^][,.$''\*&!%^{}?a-zA-Z0-9-]$'
       ) AS simplified_emp_address
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (emp_address) AS
SELECT '"test1"' FROM DUAL UNION ALL
SELECT '$test2$' FROM DUAL UNION ALL
SELECT '[test3]' FROM DUAL UNION ALL
SELECT 'test4' FROM DUAL UNION ALL
SELECT '|test5|' FROM DUAL;

Outputs:

EMP_ADDRESS SIMPLIFIED_EMP_ADDRESS
"test1" test1
$test2$ $test2$
[test3] [test3]
test4 test4
|test5| test5

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

Related Topics

Oracle SQL Date Range Intersections
Using a SQL Server for Application Logging. Pros/Cons
How to Use T-SQL Full-Text Search to Get Results Like Google
Why Is My Left Join Not Returning Nulls
Try_Convert Fails on SQL Server 2012
Running a SQLite3 Script from Command Line
How to Best Handle the Storage of Historical Data
Query to Find All Fk Constraints and Their Delete Rules (Sql Server)
Query to Convert from Datetime to Date MySQL
Any Disadvantages to Bit Flags in Database Columns
Restoring a Database from .Bak File on Another Machine
MySQL Correlated Subquery in Join Syntax
How to Execute SQL Queries in Apache Spark
Quickest/Easiest Way to Use Search/Replace Through All Stored Procedures
MySQL Join Two Tables Count and Sum from Second Table
How to Use Left & Right Functions in SQL to Get Last 3 Characters
Create a Sqlite View Where a Row Depends on The Previous Row
Sql Server 2005 Get First and Last Date for Any Month in Any Year
Leave a reply

Submit

EMP_ADDRESS	SIMPLIFIED_EMP_ADDRESS
"test1"	test1
$test2$	$test2$
[test3]	[test3]
test4	test4
\|test5\|	test5