CODEWITHSUNDEEP
mysql
user1911703
user1911703

Reputation: 750

how to replace multiple characters from a column of table in mysql

i have a table containing more then 90,000 records where one field is phone_no.

i would like to replace the following special characters from phone_no column.

"(",")","/"," ","-","+"

the following query update only 1 character at a time. 

//SQL Query i have used to update 
UPDATE notary_info SET mobile_phone = REPLACE(mobile_phone, '/', '')

is it possible to replace all above mentioned special characters in one mysql query?

Upvotes: 5

Views: 22302

Answers (8)

Nono
Nono

Reputation: 7302

REGEXP_REPLACE

-- MySQL Query  to update 
UPDATE 
  notary_info 
SET 
  mobile_phone = REGEXP_REPLACE(
    mobile_phone, '[^[:digit:]]+', ''
  );

Almost for all bugging characters-

SELECT REGEXP_REPLACE(column, '[\("\'%[[:blank:]]&:–,#$@!;\\[\\]\)<>\?\*\^]+','')

MySQL built-in function (MySQL version 8+): REGEXP_REPLACE (string, patterns, replace-with)

SELECT 
  REGEXP_REPLACE(
    '8\"8/7\'+7:6-65+54  4', '[^[:digit:]]+', -- replace with '' if chars are not digits
    ''
  ) as mobile;

| mobile     |
|------------|
| 8877665544 |

SELECT 
  REGEXP_REPLACE(
    '8\"8/7\'+7:6-65+54  4', '["\'()/[:blank:]-+:]+', -- replace with '' if chars are in given pattern
    ''
  ) as mobile;

| mobile     |
|------------|
| 8877665544 |

Note - last '+' is for repeated chars like ++++++ or ------

And to find multiple characters, patterns work like this:

[
    (               -- open parenthesis     
    "               -- double quotes        
    \'              -- single quotes        
    %               -- percentage       
    [:blank:]       -- space                
    &               -- ampersand        
    :               -- colon
    –               -- non ASCII character  
    )               -- close parenthesis        
]

Notice the [:blank:] and [:digit:], those are a [:character_class:] and MySQL does support many names.

|   Character Class Name    |   Meaning                               |
|---------------------------|-----------------------------------------|
|   alnum                   |   Alphanumeric characters               |
|   alpha                   |   Alphabetic characters                 |
|   blank                   |   Whitespace characters                 |
|   cntrl                   |   Control characters                    |
|   digit                   |   Digit characters                      |
|   graph                   |   Graphic characters                    |
|   lower                   |   Lowercase alphabetic characters       |
|   print                   |   Graphic or space characters           |
|   punct                   |   Punctuation characters                |
|   space                   |   Space, tab, newline, and carriage return|
|   upper                   |   Uppercase alphabetic characters       |
|   xdigit                  |   Hexadecimal digit characters          |

Upvotes: 0

Sunny S.M
Sunny S.M

Reputation: 5998

create a stored function that will strip special character using their ASCII code :

DROP FUNCTION IF EXISTS `cleanString`; 
DELIMITER ;; 
CREATE FUNCTION `cleanString`(`in_str` text) RETURNS text CHARSET utf8 
BEGIN
/** 
 * Function will strip all non-ASCII and unwanted ASCII characters in string 
 * 
 * @author Sunny Attwal 
 * 
 * @param text in_arg 
 * @return text
 */ 
      DECLARE out_str text DEFAULT ''; 
      DECLARE c text DEFAULT ''; 
      DECLARE pointer INT DEFAULT 1; 

      IF ISNULL(in_str) THEN
            RETURN NULL; 
      ELSE
            WHILE pointer <= LENGTH(in_str) DO 

                  SET c = MID(in_str, pointer, 1); 

                  IF (ASCII(c) NOT IN(33,34,35,36,37,38,39,40,41,42,43,44,63,126)) THEN
                      SET out_str = CONCAT(out_str, c); 
                  ELSE
                      SET out_str = CONCAT(out_str, ' ');   
                  END IF; 

                  SET pointer = pointer + 1; 
            END WHILE; 
      END IF; 

      RETURN out_str; 
END
;; 
DELIMITER ;

NOTE : add spacial character ascii code inside NOT IN condition, for reference find spacial char ascii code here http://www.ascii.cl/htmlcodes.htm

Run sql query as: UPDATE notary_info SET mobile_phone = cleanString(mobile_phone)

Upvotes: 0

J1MF0X
J1MF0X

Reputation: 709

If you can create functions in mysql:

DELIMITER $$
CREATE FUNCTION `replaceEx`(in_value varchar(4000), chars varchar(100), replace_char varchar(1)) RETURNS varchar(4000)
BEGIN
    DECLARE res varchar(4000);
    declare count int;
    set res = in_value;
    set count = char_length(chars);
    WHILE (count > 0) DO
      set res = replace(res,SUBSTRING(chars, count, 1),replace_char);
      set count = count - 1;
    END WHILE;
    RETURN res;
END$$
DELIMITER ;

usage: select replaceEx('mooxmyoolzand','xyz',''); returns "moomooland".

Hope this helps someone else

Upvotes: 1

Umar Adil
Umar Adil

Reputation: 5277

Replace below characters 

   ~ ! @ # $ % ^ & * ( ) _ +
    ` - = 
    { } |
    [ ] \
    : " 
    ; '

    < > ?
    , .

with this SQL

SELECT note as note_original, 

REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(
                                                    REPLACE(
                                                        REPLACE(
                                                            REPLACE(
                                                                REPLACE(
                                                                    REPLACE(
                                                                        REPLACE(
                                                                            REPLACE(
                                                                                REPLACE(
                                                                                    REPLACE(
                                                                                        REPLACE(
                                                                                            REPLACE(
                                                                                                REPLACE(
                                                                                                    REPLACE(
                                                                REPLACE(
                                                                    REPLACE(
                                                                        REPLACE(
                                                                            REPLACE(
                                                                                REPLACE(
                                                                                    REPLACE(
                                                                                        REPLACE(note, '\"', ''),
                                                                                    '.', ''),
                                                                                '?', ''),
                                                                            '`', ''),
                                                                        '<', ''),
                                                                    '=', ''),
                                                                '{', ''),
                                                                                                    '}', ''),
                                                                                                '[', ''),
                                                                                            ']', ''),
                                                                                        '|', ''),
                                                                                    '\'', ''),
                                                                                ':', ''),
                                                                            ';', ''),
                                                                        '~', ''),
                                                                    '!', ''),
                                                                '@', ''),
                                                            '#', ''),
                                                        '$', ''),
                                                    '%', ''),
                                                '^', ''),
                                            '&', ''),
                                        '*', ''),
                                    '_', ''),
                                '+', ''),
                            ',', ''),
                        '/', ''),
                    '(', ''),
                ')', ''),
            '-', ''),
        '>', ''),
    ' ', '-'),
'--', '-') as note_changed FROM invheader

Upvotes: -1

Mark
Mark

Reputation: 8451

Try nested REPLACE() function like:

UPDATE notary_info SET mobile_phone = 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(mobile_phone, '/', ''),'(',''),')',''),' ',''),'+',''),'-','');

Upvotes: 8

Explosion Pills
Explosion Pills

Reputation: 191809

Unfortunately, MySQL does not allow you to replace multiple characters simultaneously with a single statement. You can chain REPLACE calls:

REPLACE(REPLACE(mobile_phone, "/", ""), "(", "")

It sounds like you are trying to avoid that though. In that case, it may be better to use a scripting language to go through each result of your query and do the replacement itself. Many languages can do this simply, for instance in PHP:

strtr($row['mobile_phone'], array("()/ -+" => ""))

Upvotes: 4

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

You will have to chain the REPLACE calls. For example:

UPDATE notary_info SET mobile_phone = REPLACE(REPLACE(mobile_phone, '/', ''), '+')

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

UPDATE notary_info SET mobile_phone = 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(mobile_phone, '/', ''),'(',''),')',''),' ',''),'+',''),'-','');

SAMPLE FIDDLE

Upvotes: 1

Related Questions