Mysql make slug function

New update on slugify

DROP FUNCTION IF EXISTS `slugify`;
DELIMITER ;;
CREATE DEFINER=CURRENT_USER
FUNCTION `slugify`(dirty_string varchar(200))
RETURNS varchar(200) CHARSET latin1
DETERMINISTIC
BEGIN
    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/

 

Like this post? Share it!

digg 48 Mysql make slug function reddit 48 Mysql make slug function stumbleupon 48 Mysql make slug function delicious 48 Mysql make slug function furl 48 Mysql make slug function technorati 48 Mysql make slug function google 48 Mysql make slug function myspace 48 Mysql make slug function facebook 48 Mysql make slug function twitter 48 Mysql make slug function

About Clay

I am Clay who is the main writer for this website. I own a small web hosting company in Malaysia and i'm available to be hired as individual contractor on elance or odesk. You can find me on twitter.
This entry was posted in How-to, SQL. Bookmark the permalink.

Comments are closed.