Easy way of generating a slug name column from the name column?
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 !
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`
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, '~', '');