Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 32646
  • Last Modified:

SQL REPLACE for multiple characters

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
jeaney
Asked:
jeaney
2 Solutions
 
chedgeyCommented:
Messy, but how about:

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

Regards

Chedgey
0
 
jeaneyAuthor Commented:
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
 
MikeOM_DBACommented:
Try TRANSLATE:

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

TRANSLATE('A
------------
ABCUYTUYTUYT
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeOM_DBACommented:

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

0
 
jeaneyAuthor Commented:
Thanks Mike but I'm being told that TRANSLATE is not a recognised function name. I'm using MSSQl
0
 
MikeOM_DBACommented:
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
 
rherguthCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now