How to Remove Special Characters in Column With MySQL

Remove special characters from a database field

update mytable
set FieldName = REPLACE(FieldName,'/','')

That's a good place to start.

How to remove special characters in column with MySQL?

I think we can do this with a single query:

UPDATE yourTable
SET task_users = SUBSTRING(
    REPLACE(CONCAT(',', task_users, ','), CONCAT(',', uid, ','), ','),
    2,
    LENGTH(task_users) - LENGTH(uid) - 1)
WHERE task_users REGEXP CONCAT('[[:<:]]', uid, '[[:>:]]');

Here is a link to a demo (to be used only for testing purposes):

Demo

This answer uses a trick, by which we append commas to the start and end of the task_users string. Then, we compare a given user ID by also appending commas to its start and end. If a match is found, we replace with just a single comma. But, this leaves the replacement still with its starting and ending commas, so we remove those with a substring operation.

SQL Olympics aside, hopefully you can see by the complexity of these answers that working with CSV data in a SQL database can be a real headache. Maybe you can even use this page as evidence to your colleagues that the table design needs to change.

how to remove special characters from mysql field name

I thought you couldn't write to INFORMATION_SCHEMA because of a permission issue, but after reading the MySQL Manual I realise this is expected behavior as the manual states:

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

To achieve a table rename by using the RENAME TABLE command, first run a select query to find all the tables that need changing and then rename them replacing the carnage return with a space character.

To rename just a column from within a table the ALTER TABLE command can be used with the CHANGE COLUMN parameters, for example:

ALTER TABLE table_name CHANGE COLUMN 'Date\nStart' 'Date Start' DATETIME 

I know you've already said that is the command you need, so I've tested this myself by firstly selecting the tables and then running the ALTER TABLE command and it worked fine. I was using the command line interface, so maybe the problem lies with phpMyAdmin - can you confirm it isn't encoding or escaping \n?

Here is what I tested via the command line and worked OK:

SELECT COLUMN_NAME
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE TABLE_SCHEMA = 'test_345'
AND TABLE_NAME LIKE '%\n%';
ALTER TABLE test_table1 CHANGE COLUMN 'Date\nStart' 'Date Start' DATETIME;

Either of these could be wrapped up into a routine should you think this would be useful in the future.

Sql Query to remove special Characters

MySQL doesn't support such a query. Your best shot within a MySQL query is a lot of the following:

update TABLENAME
set FIELDNAME = replace(FIELDNAME,'_','')

mysql SELECT where FIELD alphanumerically match with WHERE IN, remove special characters while matching

Using builtin MySQL functions, we could "remove" occurrences of a character using the REPLACE() function, but it would be a separate function call for each character.

Given the sample data, looks like there are only two characters that need to be removed, the slash and the dash characters.

As a demonstration, consider:

 SELECT t.order_number
      , REPLACE(REPLACE(REPLACE( t.order_number ,'/',''),'-',''),' ','') AS rep_order_number
   FROM ( SELECT 'ASD-12/53-DF' AS order_number
          UNION ALL SELECT 'FVB 1256DF'
          UNION ALL SELECT 'FCB/150/KL'
        ) t

returns:

order_number  rep_order_number
------------  ----------------
ASD-12/53-DF  ASD1253DF
FVB 1256DF    FVB1256DF
FCB/150/KL    FCB150KL

---

If we're going to use an expression like that (with a column wrapped in REPLACE functions), that will require the expression to evaluated on every value of the column; it can't make use of an index.

 WHERE expr IN ('fee','fi','fo','fum')

---

For performance, we would prefer to materialize that expression as a column (or derived column) in the table, so we can make use of an index.

---

In terms of removing special characters, the example expression only removes the specified characters. If an order number were to contain an asterisk character, that would not be removed.

We need to consider the question: do we want to blacklist the characters we want to exclude, or do we want to whitelist the characters we want to include.

For whitelisting allowed characters, consider a user defined function. Here's an example that allows digits 0 thru 9, upper and lower case letters, and underscore character:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `udf_strip_alphanum`(as_arg VARCHAR(2000)
) RETURNS VARCHAR(2000) CHARSET latin1
    DETERMINISTIC
BEGIN
-- #############################################################################
-- Purpose: return string contains only specified set of characters
-- other characters removed
-- #############################################################################
   DECLARE ls_char CHAR(1)        DEFAULT '';
   DECLARE ls_ret  VARCHAR(2000)  DEFAULT '';
   DECLARE _i      INT            DEFAULT 0;
   DECLARE _len    INT            DEFAULT 0;
   SET _len := CHAR_LENGTH(as_arg);
   -- early exit for zero length or null
   IF _len IS NULL OR _len = 0 THEN
      RETURN as_arg;
   END IF;
   -- safety net for input string over 2000 character limit:
   IF _len > 2000 THEN
      SET _len := 2000;
   END IF;
   WHILE _i < _len DO
      SET _i := _i + 1;
      SET ls_char := SUBSTRING(as_arg,_i,1);
      IF INSTR('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_',ls_char) THEN
         SET ls_ret := CONCAT(ls_ret,ls_char);
      END IF;
   END WHILE;
   RETURN ls_ret;
END$$

DELIMITER ; 

The list of allowed characters is easy to modify.

 SELECT t.order_number
      , udf_strip_alphanum( t.order_number )
   FROM ( SELECT 'ASD-12/53-DF' AS order_number
          UNION ALL SELECT 'FVB 1256DF'
          UNION ALL SELECT 'FCB/150/KL'
        ) t

Remove escape symbols in string using mysql command

Use REPLACE. For harder things REGEXP_REPLACE.

SELECT REPLACE(somecolumn, '\"', '"')
SELECT REGEXP_REPLACE('"..."', '(^"|"$)', '')

The latter will unquote the entire string, as ^ is the start, and $ the end.

BTW I would actually correct all the data in the table once. (After a backup.)

---

Related Topics