mysql - Easy way of generating a slug name column from the name column?

Question

db_products table:

| ID | Name         |
| 40 | Foo Bar!~~~~ |

I want to generate a slug name column:

| ID | Name         | Slug_Name |
| 40 | Foo Bar!~~~~ | foo-bar   |

Can this be actually done via SQL? Or would I need to write a script using a different language?

EDIT: I'm generating slugs in PHP using this function:

function toSlug($string,$space="-") {
    if (function_exists('iconv')) {
        $string = @iconv('UTF-8', 'ASCII//TRANSLIT', $string);
    }
    $string = preg_replace("/[^a-zA-Z0-9 -]/", "", $string);
    $string = strtolower($string);
    $string = str_replace(" ", $space, $string);
    return $string;
}

So far my SQL skills fairly basic.

ALTER TABLE db_products ADD Slug_Name VARCHAR

How do I loop through each row and set Slug_Name = toSlug(Name) but in SQL?

Answer 1

Here is an easy solution with a single query :

UPDATE `my_table` SET alias = lower(name),
alias = replace(alias, '.', ' '),
alias = replace(alias, ''', '-'),
alias = replace(alias,'?','s'),
alias = replace(alias,'D','Dj'),
alias = replace(alias,'?','z'),
alias = replace(alias,'T','B'),
alias = replace(alias,'?','Ss'),
alias = replace(alias,'à','a'),
alias = replace(alias,'á','a'),
alias = replace(alias,'a','a'),
alias = replace(alias,'?','a'),
alias = replace(alias,'?','a'),
alias = replace(alias,'?','a'),
alias = replace(alias,'?','a'),
alias = replace(alias,'?','c'),
alias = replace(alias,'è','e'),
alias = replace(alias,'é','e'),
alias = replace(alias,'ê','e'),
alias = replace(alias,'?','e'),
alias = replace(alias,'ì','i'),
alias = replace(alias,'í','i'),
alias = replace(alias,'?','i'),
alias = replace(alias,'?','i'),
alias = replace(alias,'e','o'),
alias = replace(alias,'?','n'),
alias = replace(alias,'ò','o'),
alias = replace(alias,'ó','o'),
alias = replace(alias,'?','o'),
alias = replace(alias,'?','o'),
alias = replace(alias,'?','o'),
alias = replace(alias,'?','o'),
alias = replace(alias,'ù','u'),
alias = replace(alias,'ú','u'),
alias = replace(alias,'?','u'),
alias = replace(alias,'y','y'),
alias = replace(alias,'y','y'),
alias = replace(alias,'t','b'),
alias = replace(alias,'?','y'),
alias = replace(alias,'?','f'),
alias = replace(alias, '?', 'oe'),
alias = replace(alias, '€', 'euro'),
alias = replace(alias, '$', 'dollars'),
alias = replace(alias, '￡', ''),
alias = trim(alias),
alias = replace(alias, ' ', '-'),
alias = replace(alias, '--', '-') ;

In this example :

• 'my_table' is the name of the table,
• 'name' is the original field
• 'alias' is the name of my slug field

Hope it helps !