How to create a WordPress compatible slug in MySQL

Sometimes it is convenient to import terms or posts into a WordPress database.  When you do, you may need to create a ‘slug’ column. To do this, I’ve modified a user defined function I found on StackOverflow or some similar site (possibly here: http://stackoverflow.com/questions/5409831/mysql-stored-function-to-create-a-slug/5410316 ).  That version had some bugs. So I fixed them.

Here is my final script to create the UDF slugify.

Usage: select slugify(name), name from externaldata;

CREATE DEFINER=`root`@`localhost` FUNCTION `slugify`(`dirty_string` VARCHAR(200)) RETURNS varchar(200) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE x, y , z Int;
Declare temp_string, allowed_chars, new_string VarChar(200);
Declare is_allowed Bool;
Declare c, check_char VarChar(1);

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;

Return temp_string;
END