MySql replacing spaces with hyphens

Question

A day ago, I asked this question on stackoverflow. Sure, that works well, but does anyone know how I can do the same thing in a MySql statement without any php involved?

Eg: select preg_replace(:songName,' ', '-') //Ugh, this is wrong.

What I'm trying to do Is replace spaces with a -. But sometimes, when there is a space, I'll get more -

Eg: Metallica - Hero of the Day ends up as Metallica---Hero-of-the-Day

Any chance of making it just: Metallica-Hero-of-the-Day

BTW: It's not only song names I'm replacing.

I'm ok with a simple MySql replace, but I can see doing the above is going to need more than that.

as you know how to use replace for mysql do as follow: replace every `-` for a space, then two spaces for none spaces then one space for a `-` — Jorge Campos, Nov 13 '13 at 04:45

Bohemian · Answer 1 · 2013-11-13T08:25:17.630

3

I would replace spaces with hyphens first, then deal with any multiple hyphens that may have been created:

select replace(replace(replace(songTitle, ' ', '-'), '---', '-'), '--', '-')

I've replaced --- and -- separately because there are edge cases which overall would require both, and in that order.

See SQLFiddle

edited Nov 13 '13 at 08:25

answered Nov 13 '13 at 06:15

Bohemian

412,405
93
575
722

@PraveenPrasannan that was a typo on my part. Corrected now - see fiddle. NOTE: the hyphen in your fiddle was *not* a standard hyphen - it's a weird Microsoft dash character, which will not be replaced by my code. I changed the character for my fiddle – Bohemian Nov 13 '13 at 08:24
@PraveenPrasannan now the whitespace character between "Hero" and "of" is not a space! (It's a tab or a non-breaking space... who knows). Manually type in your examples - don't copy paste from a Microsoft special character infested filename. – Bohemian Nov 13 '13 at 10:48
those were pure white spaces. but 9 concecutive. thats all – Praveen Prasannan Nov 13 '13 at 10:53

Praveen Prasannan · Answer 2 · 2013-11-29T05:08:33.760

Use a user defined function like this(use delimetres accordingly)

CREATE FUNCTION replace_spaceWithHyphen(textToReplace varchar(100))
RETURNS TEXT
BEGIN
DECLARE occHyphen int; 
DECLARE occSpace int; 
set occHyphen = 1;
set occSpace = 1;
WHILE (occHyphen <> 0 || occSpace <> 0) DO
        SELECT LOCATE('--',textToReplace) into occHyphen;
        SELECT LOCATE(' ',textToReplace) into occSpace;
        SELECT REPLACE(textToReplace,' ','-') into textToReplace;
        SELECT REPLACE(textToReplace,'--','-') into textToReplace;
    END WHILE;
  RETURN textToReplace;
END;

Then call your select like this:

SELECT replace_spaceWithHyphen('Metallica - Hero of the Day');

Answer would be:

TEXT
Metallica-Hero-of-the-Day

SAMPLE FIDDLE

score 0 · Answer 3 · answered Nov 13 '13 at 04:49

0

This should work:

select 
replace(
replace(
replace('Metallica - Hero of the Day', '-', ' ')
  , '  ', '')
  , ' ', '-')

answered Nov 13 '13 at 04:49

Jorge Campos

22,647
7
56
87

There's no way to use something like a preg_replace in MySQL as we did in the previous answer? – jmenezes Nov 13 '13 at 04:52
You can use a UDF (User Defined Function) to do this. Pure Mysql You cant as in this answer: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – Jorge Campos Nov 13 '13 at 04:54

Muhammad Rizwan Kaim Khani · Answer 4 · 2013-11-13T06:47:17.927

You may write your query. It is so easy for you.

Suppose you have a table named class(id, classname) with two fields. Now you insert in your table in classname field i.e. Metallica - Hero of the Day.

Now you can execute this by below given program.

mysql_connect('localhost','root','');
mysql_select_db('dbname');            // Please insert your dbname

$query = mysql_query('SELECT classname, REPLACE(classname," ","-") from class');
$record = mysql_fetch_array($query);
echo $record['REPLACE(classname," ","-")'];

It will give output. i.e. Metallica---Hero-of-the-Day.

and if you replace your query with. I have taken help from Bohemian answer for below query.

$query = mysql_query("SELECT classname, replace(replace(replace(classname, ' ', '-'), '---', '-'), '--', '') from class");
$record = mysql_fetch_array($query);
echo $record["replace(replace(replace(classname, ' ', '-'), '---', '-'), '--', '')"];

You will get result i.e. Metallica-Hero-of-the-Day

That's it. Easy.

Thanks

Phoenix · Answer 5 · 2019-04-17T11:05:53.620

0

You can try this method.

UPDATE TABLE_NAME SET column_name = REPLACE(column_name, old_value, new_value);

For Example

UPDATE TABLENAME SET column_name = REPLACE(column_name, '-', ' ');

Hope this will helps you.

edited Apr 17 '19 at 11:05

answered Feb 22 '17 at 11:46

Phoenix

1,470
17
23

MySql replacing spaces with hyphens

5 Answers5