?
Solved

SQL REPLACE for multiple characters

Posted on 2005-03-23
7
Medium Priority
?
31,269 Views
Last Modified: 2011-08-18
Hi

I want to remove multiple characters (that are not necessary sequential) in a string.

To remove the character 'a' from a string I use the following:

REPLACE(mystring, 'a', '')

But if I want to remove all 'a's 'b's and 'c's how do I do it?
The following does not work but displays the logic I need:

REPLACE(mystring, 'a' | 'b' | 'c', '')

Fast reponse greatly appreciated!
0
Comment
Question by:jeaney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Expert Comment

by:chedgey
ID: 13612892
Messy, but how about:

Replace( Repalce( Replace( MyString,'a','' ),'b','' ),'c','' )

Regards

Chedgey
0
 
LVL 1

Author Comment

by:jeaney
ID: 13613834
Mmmmh, thanks chedgey. I did think about doing it this way but was hoping there was a better way :(

Do you think this is the only way it can be done?

Will hold out to see if anyone else has any other suggestions.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 13614463
Try TRANSLATE:

Select translate('AaBbCcUYTUYTUYT','~abc','~') From Dual;

TRANSLATE('A
------------
ABCUYTUYTUYT
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 13614473

PS: Note the required '~' character (or any other you want).

0
 
LVL 1

Author Comment

by:jeaney
ID: 13620230
Thanks Mike but I'm being told that TRANSLATE is not a recognised function name. I'm using MSSQl
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 225 total points
ID: 13621776
OK there seems to be no other option to REPLACE() but to code your own function, I'm not familiar with MySQL syntax but it would (paraphrased) look kinda like this:

CREATE OR REPLACE FUINCTION TRANSLATE (in in_str char, in trns_str char)
RETURN CHAR
BEGIN
  DECLARE I,  N  INT;
  DECLARE out_str CHAR;
  SET N= LENGTH(trns_str);
  SET out_str = in_str);
  SET I = 1;
  L1: LOOP
    SET out_str = REPLACE(out_str,SUBSTR(trns_str,I,1),'');
    SET I = I + 1;
    IF I > LENGTH(trns_str) THEN LEAVE L1; END IF;
  END LOOP;
  RETURN out_str;
END;
   

PS:
0
 
LVL 9

Accepted Solution

by:
rherguth earned 225 total points
ID: 13648649
Here is a function:
SELECT dbo.fwt_StringReduce('Bob Herguth@#$^&*()', '@#$^&*()')


/*
Purpose:
      Returns a string devoid of the given characters.
Type:
      Scalar Return
Prototype:
      Print dbo.fwt_StringReduce('Bob Herguth@#$^&*()', '@#$^&*()')
*/
CREATE FUNCTION fwt_StringReduce
      (
      @inputstr VARCHAR(4096)
      , @stripchrs VARCHAR(255)
      )
RETURNS VARCHAR(4096)
AS  
BEGIN
      DECLARE @charcounter INT
      SET @charcounter = 1
      WHILE  @charcounter <= LEN(@stripchrs)
      BEGIN
            SET @inputstr = REPLACE(@inputstr, SUBSTRING(@stripchrs, @charcounter, 1), '')
            SET @charcounter = @charcounter + 1
      END
RETURN @inputstr
END
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question