new update on slugify
DROP FUNCTION IF EXISTS `slugify`;DELIMITER ;;CREATE DEFINER=CURRENT_USERFUNCTION `slugify`(dirty_string varchar(200))RETURNS varchar(200) CHARSET latin1DETERMINISTICBEGIN DECLARE x, y , z, i Int; Declare temp_string, allowed_chars, new_string VarChar(200); Declare is_allowed Bool; Declare c, check_char VarChar(1); set i = 0; set allowed_chars = "abcdefghijklmnopqrstuvwxyz0123456789-"; set temp_string = LOWER(dirty_string); Select temp_string Regexp('&') Into x; If x = 1 Then Set temp_string = replace(temp_string, '&', ' and '); End If; Select temp_string Regexp('[^a-z0-9]+') into x; If x = 1 then set z = 1; While z <= Char_length(temp_string) Do Set c = Substring(temp_string, z, 1); Set is_allowed = False; Set y = 1; Inner_Check: While y <= Char_length(allowed_chars) Do If (strCmp(ascii(Substring(allowed_chars,y,1)), Ascii(c)) = 0) Then Set is_allowed = True; Leave Inner_Check; End If; Set y = y + 1; End While; If is_allowed = False Then Set temp_string = Replace(temp_string, c, '-'); End If; set z = z + 1; End While; End If; Select temp_string Regexp("^-|-$|'") into x; If x = 1 Then Set temp_string = Replace(temp_string, "'", ''); Set z = Char_length(temp_string); Set y = Char_length(temp_string); Dash_check: While z > 1 Do If Strcmp(SubString(temp_string, -1, 1), '-') = 0 Then Set temp_string = Substring(temp_string,1, y-1); Set y = y - 1; Else Leave Dash_check; End If; Set z = z - 1; End While; End If; Repeat Select temp_string Regexp("--") into x; If x = 1 Then Set temp_string = Replace(temp_string, "--", "-"); End If; Until x <> 1 End Repeat; If LOCATE('-', temp_string) = 1 Then Set temp_string = SUBSTRING(temp_string, 2); End If;SELECT COUNT(*) INTO i FROM fanpage WHERE slug LIKE CONCAT(temp_string,'%');If i > 0 Then Set temp_string = CONCAT(temp_string,'-',i+1);End If;Return temp_string;END;;DELIMITER ;P.S: change “fanpage” to your own table – this is to prevent duplicate on slug
credit goes to http://nastyhabit.wordpress.com/2008/09/25/mysql-slug-maker-function-aka-the-slugifier/
