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

mysql, sql

Solution

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, '~', '');

Problem

`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?

Original source