Mysql make slug function

New update on slugify

FUNCTION `slugify`(dirty_string varchar(200))
RETURNS varchar(200) CHARSET latin1
    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;
                Leave Dash_check;
            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, "--", "-");
        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;

P.S: change "fanpage" to your own table - this is to prevent duplicate on slug

credit goes to

Like this post? Share it!

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.