Remove all characters from the string

Question

I have a MySQL database with about 20.000 entries where files are named Name_Subname_XXXXX. Because the files are named that way, the name that shows to the internet itself also get's that name when entered into the database.

I wonder how can I in an easy way remove the _ from the name and just keep Name Subname XXXXX?

possible duplicate of [How can I use mySQL replace() to replace strings in multiple records?](http://stackoverflow.com/questions/4271186/how-can-i-use-mysql-replace-to-replace-strings-in-multiple-records) — Alex K., Jun 17 '15 at 11:50

score 0 · Answer 1 · answered Jun 17 '15 at 11:49

0

You can use replace:

select replace(col, '_', ' ') from tbl

answered Jun 17 '15 at 11:49

potashin

44,205
11
83
107

Will that work for all _ ? it varies from X_X_X_X_ to X_X. – Rizzzler Jun 17 '15 at 13:21
@Rizzzler: yes, it will. – potashin Jun 17 '15 at 13:22

score 0 · Accepted Answer · answered Jun 17 '15 at 11:50

0

You can use MySQL's REPLACE method for strings:

UPDATE tbl SET filename = REPLACE(filename, '_', ' ');

answered Jun 17 '15 at 11:50

wonderb0lt

2,035
1
23
37

Will that work for all _ ? it varies from X_X_X_X_ to X_X. – Rizzzler Jun 17 '15 at 13:21
@Rizzler Yes, it will. – wonderb0lt Jun 17 '15 at 13:24

score 0 · Answer 3 · answered Jun 17 '15 at 11:51

0

use replace()

SELECT REPLACE(col, '_', ' ') from table;

Ref https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

answered Jun 17 '15 at 11:51

Tamil Selvan C

19,913
12
49
70

score 0 · Answer 4 · answered Jun 17 '15 at 11:54

I am guessing that you probably want to split the name into three columns (as opposed to just removing an underscore in a name). If so, use substring_index():

select substring_index(filename, '_', 1) as name,
       substring_index(substring_index(filename, '_', 2), '_', -1) as subname,
       substring_index(filename, '_', -1) as xxxxx

Remove all characters from the string

4 Answers4