Today I had to fix a nasty issue in one of my PHP-based websites: to put it into few words, I needed to find a way to trasform a number of post titles into slugs, meaning that I had to remove accents, diacritics, special characters and so on and have them replaced with URL-valid characters such as hypens and underscore.
Here's the function I came up with:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE FUNCTION toSlug(s NVARCHAR(500)) RETURNS NVARCHAR(500) DETERMINISTIC RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(LOWER(TRIM(s)), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '/', ''), '"', ''), '?', ''), "'", ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'),'ù','u'), 'ú','u'),'û','u'),'ü','u'),'ý','y'),'ë','e'),'à','a'),'á','a'),'â','a'),'ã','a'), 'ä','a'),'å','a'),'æ','a'),'ç','c'),'è','e'),'é','e'),'ê','e'),'ë','e'),'ì','i'), 'í','i'),'ě','e'), 'š','s'), 'č','c'),'ř','r'), 'ž','z'), 'î','i'),'ï','i'),'ð','o'), 'ñ','n'),'ò','o'),'ó','o'),'ô','o'),'õ','o'),'ö','o'),'ø','o'),'%', ''); |
That's basically it.
Once created, it can be used in the following way:
|
1 |
SELECT toSlug("Try Me With This String containing a Bunch of Pesky Characters at the end! àéìòù?/("); |
It should return the following valid slug:
|
1 |
try-me-with-this-string-containing-a-bunch-of-pesky-characters-at-the-end-aeiou |
This function was more than enough to save my day: if you stumbled upon this post, I sincerely hope that it will be the same for you!
