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?

What have you tried so far? MySQL has several string manipulation functions. You may want to look at replace(), lower(), trim() and regexp() specifically. — Jared, Apr 17 '13 at 16:28

Erwan · Answer 1 · 2016-01-05T12:24:58.090

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,'Ð','Dj'),
alias = replace(alias,'ž','z'),
alias = replace(alias,'Þ','B'),
alias = replace(alias,'ß','Ss'),
alias = replace(alias,'à','a'),
alias = replace(alias,'á','a'),
alias = replace(alias,'â','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,'ð','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'),
alias = replace(alias,'ý','y'),
alias = replace(alias,'þ','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 !

Error on line 5, you are missing a comma at the end. – Slam Jan 02 '16 at 03:54 — Slam, Jan 02 '16 at 03:54

score 4 · Accepted Answer · edited Jul 01 '19 at 21:22

You can certainly do a string replace using MySQL. The official documentation lists quite a few string functions you might find useful.

SELECT REPLACE('Foo Bar!~~~~', '~', '');
SELECT LOWER('Foo Bar!');

I also ran across this blog post on using regular expressions in MySQL.

Updated: Details from the blog post I mentioned:

So what I would recommend is creating a function for doing a regular expression replace:

DELIMITER $$
FUNCTION `regex_replace`(pattern varchar(1000),replacement varchar(1000),original varchar(1000))
RETURNS varchar(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = original;
IF original REGEXP pattern THEN
    SET temp = "";
    loop_label: LOOP
    IF i>CHAR_LENGTH(original) THEN
        LEAVE loop_label;
    END IF;
    SET ch = SUBSTRING(original,i,1);
    IF NOT ch REGEXP pattern THEN
        SET temp = CONCAT(temp,ch);
    ELSE
        SET temp = CONCAT(temp,replacement);
    END IF;
    SET i=i+1;
END LOOP;
END IF;
RETURN temp;
END$$
DELIMITER ;

Then something akin to the following

SELECT regex_replace('[^a-zA-Z0-9]+', '', '%$&?/’|test><+-,][)(' )

If you're not comfortable with that approach, you can always just run some update calls using replace

update db_products set Slug_Name = replace(Name, '~', '');

Combined with `TRIGGER` this could be very effective. Never used one, but seems like it should be something like `CREATE TRIGGER BEFORE INSERT FOR EACH ROW SET new.slug = COOL_REPLACE_FUNCTION(new.title);` — aufziehvogel, Apr 17 '13 at 16:37
So, help me understand what you're asking for. You have what looks like a perfectly good solution in PHP. You're wanting to update rows already in your database? — Arth Du, Apr 17 '13 at 17:31
No, create a new column that contains slug names for each respective row. — meiryo, Apr 18 '13 at 17:07

score 2 · Answer 3 · answered May 05 '18 at 14:12

Change your tbl_name and field_name and slug_field_name

SELECT field_name,
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 LOWER(TRIM(field_name)), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')
AS `slug_field_name`
FROM tbl_name

For test 'Your String' result your-string:

SELECT
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 LOWER(TRIM('Your String')), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')
AS `slug_test`

For special characters:

Append a [REPLACE(] and a [, ':', '')]

For example ö character in this string "Hallo schöne Welt"

Result is "hallo-schone-welt"

SELECT
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 REPLACE(
 LOWER(TRIM('Hallo schöne Welt')), 'ö', 'o'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')
AS `slug_test`

score 1 · Answer 4 · answered Apr 25 '17 at 16:32

I made some edits to Erwan Dupeux-Maire's answer, to account for some missing characters, which I had in my name field: ',', '&', and '/'

UPDATE `table` SET slug = lower(name),
slug = replace(slug, '.', ''),
slug = replace(slug, '\'', '-'),
slug = replace(slug, '/', '-'),
slug = replace(slug,'š','s'),
slug = replace(slug,'Ð','Dj'),
slug = replace(slug,'ž','z'),
slug = replace(slug,'Þ','B'),
slug = replace(slug,'ß','Ss'),
slug = replace(slug,'à','a'),
slug = replace(slug,'á','a'),
slug = replace(slug,'â','a'),
slug = replace(slug,'ã','a'),
slug = replace(slug,'ä','a'),
slug = replace(slug,'å','a'),
slug = replace(slug,'æ','a'),
slug = replace(slug,'ç','c'),
slug = replace(slug,'è','e'),
slug = replace(slug,'é','e'),
slug = replace(slug,'ê','e'),
slug = replace(slug,'ë','e'),
slug = replace(slug,'ì','i'),
slug = replace(slug,'í','i'),
slug = replace(slug,'î','i'),
slug = replace(slug,'ï','i'),
slug = replace(slug,'ð','o'),
slug = replace(slug,'ñ','n'),
slug = replace(slug,'ò','o'),
slug = replace(slug,'ó','o'),
slug = replace(slug,'ô','o'),
slug = replace(slug,'õ','o'),
slug = replace(slug,'ö','o'),
slug = replace(slug,'ø','o'),
slug = replace(slug,'ù','u'),
slug = replace(slug,'ú','u'),
slug = replace(slug,'û','u'),
slug = replace(slug,'ý','y'),
slug = replace(slug,'ý','y'),
slug = replace(slug,'þ','b'),
slug = replace(slug,'ÿ','y'),
slug = replace(slug,'ƒ','f'),
slug = replace(slug, 'œ', 'oe'),
slug = replace(slug, '€', 'euro'),
slug = replace(slug, '$', 'dollars'),
slug = replace(slug, '£', ''),
slug = trim(slug),
slug = replace(slug, ',', ''),
slug = replace(slug, '&', ''),
slug = replace(slug, ' ', '-'),
slug = replace(slug, '--', '-');

score 0 · Answer 5 · answered Apr 27 '23 at 10:06

Here is simple and fast way to generate slug from name field:

UPDATE store_book SET
    slug = lower(title),
    slug = replace(slug, '.', ' '),
    slug = replace(slug, ',', ' '),
    slug = replace(slug, ';', ' '),
    slug = replace(slug, ':', ' '),
    slug = replace(slug, '?', ' '),
    slug = replace(slug, '%', ' '),
    slug = replace(slug, '$', ' '),
    slug = replace(slug, '&', ' '),
    slug = replace(slug, '#', ' '),
    slug = replace(slug, '*', ' '),
    slug = replace(slug, '!', ' '),
    slug = replace(slug, '_', ' '),
    slug = replace(slug, '@', ' '),
    slug = replace(slug, '+', ' '),
    slug = replace(slug, '(', ' '),
    slug = replace(slug, ')', ' '),
    slug = replace(slug, '[', ' '),
    slug = replace(slug, ']', ' '),
    slug = replace(slug, '/', ' '),
    slug = replace(slug, '-', ' '),
    slug = replace(slug, '\'', ''),
    slug = trim(slug),
    slug = replace(slug, ' ', '-'),
    slug = replace(slug, '--', '-'),
    slug=replace(slug, 'ä', 'a'),
    slug=replace(slug, 'å', 'a'),
    slug=replace(slug, 'æ', 'a'),
    slug=replace(slug, 'ç', 'c'),
    slug=replace(slug, 'è', 'e'),
    slug=replace(slug, 'é', 'e'),
    slug=replace(slug, 'ê', 'e'),
    slug=replace(slug, 'ë', 'e'),
    slug=replace(slug, 'ì', 'i'),
    slug=replace(slug, 'í', 'i'),
    slug=replace(slug, 'î', 'i'),
    slug=replace(slug, 'ï', 'i'),
    slug=replace(slug, 'ð', 'o'),
    slug=replace(slug, 'ñ', 'n'),
    slug=replace(slug, 'ò', 'o'),
    slug=replace(slug, 'ó', 'o'),
    slug=replace(slug, 'ô', 'o'),
    slug=replace(slug, 'õ', 'o'),
    slug=replace(slug, 'ö', 'o'),
    slug=replace(slug, 'ø', 'o'),
    slug = replace(slug, 'ù','u'),
    slug = replace(slug, 'ú','u'),
    slug = replace(slug, 'û','u'),
    slug = replace(slug, 'ü','u'),
    slug = replace(slug, 'ý','y'),
    slug = replace(slug, 'ë','e'),
    slug = replace(slug, 'à','a'),
    slug = replace(slug, 'á','a'),
    slug = replace(slug, 'â','a'),
    slug = replace(slug, 'ã','a'),
    slug= replace(slug, '%', ''),
    slug= replace(slug, 'ç', 'c'),
    slug= replace(slug, 'ü', 'u'),
    slug= replace(slug, 'ğ', 'g'),
    slug= replace(slug, 'ş', 's'),
    slug= replace(slug, 'ß', 'b'),
    slug= replace(slug, 'ı', 'i'),
    slug= replace(slug, '.', ''),
    slug= replace(slug, 'ö', 'ö'),
    slug= replace(slug, 'ç', 'c'),
    slug= replace(slug, '#x27;', ''),
    slug = replace(slug, '--', '-');

at the end you can use below query to validate the slug field

SELECT * FROM store_book WHERE
    slug NOT RLIKE '^([a-z0-9]+\-)*[a-z0-9]+$';

Note your last statement should be

slug = replace(slug, '--', '-')

but you can add other characters before this line. To find what you need to add you can use above validation query if it returns any rows it means there is any character which is not supported in slug.

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

5 Answers5

Linked

Related