How to rewrite this php slugify function to mysql? -
i found slugify function on here: php function make slug (url string)
and tried rewrite mysql, i've done is:
- borrowed regex_replace function from: https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/
- and transliterate function from: http://igstan.ro/posts/2009-02-13-mysql-transliteration-function.html
- rewrote mentioned @ top correct answer function symfony fw last bit of mysql create function, function.
, @ output i'm getting lowered text, no dashes , letters seem transliterated well, thing left dashes.
my query:
update `ad_kategorija` set `slug_lt`=slugify(`kat_pavlt`), `slug_ru`=slugify(`kat_pavru`), `slug_en`=slugify(`kat_paven`) functions:
# regex replace function delimiter $$ create definer=`root`@`localhost` function `regex_replace`(pattern varchar(1000),replacement varchar(1000),original varchar(1000)) returns varchar(1000) charset utf8 deterministic begin declare temp varchar(1000); declare ch varchar(1); declare int; set = 1; set temp = ''; if original regexp pattern loop_label: loop if i>char_length(original) leave loop_label; end if; set ch = substring(original,i,1); if not ch regexp pattern set temp = concat(temp,ch); else set temp = concat(temp,replacement); end if; set i=i+1; end loop; else set temp = original; end if; return temp; end$$ # transliteration function create definer=`root`@`localhost` function `transliterate`(original varchar(512)) returns varchar(512) charset utf8 begin declare translit varchar(512) default ''; declare len int(3) default 0; declare pos int(3) default 1; declare letter char(1); declare is_lower bit; set len = char_length(original); while (pos <= len) set letter = substring(original, pos, 1); set is_lower = if(lcase(letter) collate utf8_bin = letter collate utf8_bin, 1, 0); case true when letter = 'a' set letter = if(is_lower, 'a', 'a'); when letter = 'b' set letter = if(is_lower, 'b', 'b'); when letter = 'c' set letter = if(is_lower, 'c', 'c'); when letter = 'd' set letter = if(is_lower, 'd', 'd'); when letter = 'e' set letter = if(is_lower, 'e', 'e'); when letter = 'f' set letter = if(is_lower, 'f', 'f'); when letter = 'g' set letter = if(is_lower, 'g', 'g'); when letter = 'h' set letter = if(is_lower, 'h', 'h'); when letter = 'i' set letter = if(is_lower, 'i', 'i'); when letter = 'j' set letter = if(is_lower, 'j', 'j'); when letter = 'k' set letter = if(is_lower, 'k', 'k'); when letter = 'l' set letter = if(is_lower, 'l', 'l'); when letter = 'ł' set letter = if(is_lower, 'l', 'l'); when letter = 'm' set letter = if(is_lower, 'm', 'm'); when letter = 'n' set letter = if(is_lower, 'n', 'n'); when letter = 'o' set letter = if(is_lower, 'o', 'o'); when letter = 'p' set letter = if(is_lower, 'p', 'p'); when letter = 'q' set letter = if(is_lower, 'q', 'q'); when letter = 'r' set letter = if(is_lower, 'r', 'r'); when letter = 's' set letter = if(is_lower, 's', 's'); when letter = 't' set letter = if(is_lower, 't', 't'); when letter = 'u' set letter = if(is_lower, 'u', 'u'); when letter = 'v' set letter = if(is_lower, 'v', 'v'); when letter = 'w' set letter = if(is_lower, 'w', 'w'); when letter = 'x' set letter = if(is_lower, 'x', 'x'); when letter = 'y' set letter = if(is_lower, 'y', 'y'); when letter = 'z' set letter = if(is_lower, 'z', 'z'); else set letter = letter; end case; -- concat seems ignore whitespace character. workaround use -- concat_ws whitespace separator when letter whitespace. set translit = concat_ws(if(letter = ' ', ' ', ''), translit, letter); set pos = pos + 1; end while; return translit; end$$ # slug create function create definer=`root`@`localhost` function `slugify`(`dirty_string` varchar(255) charset utf8) returns varchar(255) charset utf8 deterministic begin declare temp_string varchar(255) default ''; declare output varchar(255); set temp_string = regex_replace('~[^\\pl\\d]+~u', '-', dirty_string); set temp_string = trim(both '-' temp_string); set temp_string = transliterate(temp_string); set temp_string = lower(temp_string); set temp_string = regex_replace('~[^-\\w]+~', '', temp_string); if temp_string = '' set temp_string = ''; end if; set output = temp_string; return output; end$$ delimiter ; so can me on finishing it, main problem i'm not getting dashes replaced on spaces, maybe regex wrong. or regex replace function needs addons it. please help.
fixed function, removed regex_replace usage , instead used function here: mysql stored function create slug
added transliteration tweak, final code here. transliteration function doesn't change:
delimiter $$ -- -- functions -- create definer=`root`@`localhost` function `slugify`(`dirty_string` varchar(255)) returns varchar(255) charset utf8 deterministic begin declare x, y , z int; declare temp_string, allowed_chars, new_string varchar(255); declare is_allowed bool; declare c, check_char varchar(1); set allowed_chars = "abcdefghijklmnopqrstuvwxyz0123456789-"; set temp_string = transliterate(dirty_string); set temp_string = lower(temp_string); select temp_string regexp('&') x; if x = 1 set temp_string = replace(temp_string, '&', ' , '); end if; select temp_string regexp('[^a-z0-9]+') x; if x = 1 set z = 1; while z <= char_length(temp_string) set c = substring(temp_string, z, 1); set is_allowed = false; set y = 1; inner_check: while y <= char_length(allowed_chars) if (strcmp(ascii(substring(allowed_chars,y,1)), ascii(c)) = 0) set is_allowed = true; leave inner_check; end if; set y = y + 1; end while; if is_allowed = false set temp_string = replace(temp_string, c, '-'); end if; set z = z + 1; end while; end if; select temp_string regexp("^-|-$|'") x; if x = 1 set temp_string = replace(temp_string, "'", ''); set z = char_length(temp_string); set y = char_length(temp_string); dash_check: while z > 1 if strcmp(substring(temp_string, -1, 1), '-') = 0 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("--") x; if x = 1 set temp_string = replace(temp_string, "--", "-"); end if; until x <> 1 end repeat; if locate('-', temp_string) = 1 set temp_string = substring(temp_string, 2); end if; return temp_string; end$$ create definer=`root`@`localhost` function `transliterate`(original varchar(512)) returns varchar(512) charset utf8 begin declare translit varchar(512) default ''; declare len int(3) default 0; declare pos int(3) default 1; declare letter char(1); declare is_lower bit; set len = char_length(original); while (pos <= len) set letter = substring(original, pos, 1); set is_lower = if(lcase(letter) collate utf8_bin = letter collate utf8_bin, 1, 0); case true when letter = 'a' set letter = if(is_lower, 'a', 'a'); when letter = 'b' set letter = if(is_lower, 'b', 'b'); when letter = 'c' set letter = if(is_lower, 'c', 'c'); when letter = 'd' set letter = if(is_lower, 'd', 'd'); when letter = 'e' set letter = if(is_lower, 'e', 'e'); when letter = 'f' set letter = if(is_lower, 'f', 'f'); when letter = 'g' set letter = if(is_lower, 'g', 'g'); when letter = 'h' set letter = if(is_lower, 'h', 'h'); when letter = 'i' set letter = if(is_lower, 'i', 'i'); when letter = 'j' set letter = if(is_lower, 'j', 'j'); when letter = 'k' set letter = if(is_lower, 'k', 'k'); when letter = 'l' set letter = if(is_lower, 'l', 'l'); when letter = 'ł' set letter = if(is_lower, 'l', 'l'); when letter = 'm' set letter = if(is_lower, 'm', 'm'); when letter = 'n' set letter = if(is_lower, 'n', 'n'); when letter = 'o' set letter = if(is_lower, 'o', 'o'); when letter = 'p' set letter = if(is_lower, 'p', 'p'); when letter = 'q' set letter = if(is_lower, 'q', 'q'); when letter = 'r' set letter = if(is_lower, 'r', 'r'); when letter = 's' set letter = if(is_lower, 's', 's'); when letter = 't' set letter = if(is_lower, 't', 't'); when letter = 'u' set letter = if(is_lower, 'u', 'u'); when letter = 'v' set letter = if(is_lower, 'v', 'v'); when letter = 'w' set letter = if(is_lower, 'w', 'w'); when letter = 'x' set letter = if(is_lower, 'x', 'x'); when letter = 'y' set letter = if(is_lower, 'y', 'y'); when letter = 'z' set letter = if(is_lower, 'z', 'z'); else set letter = letter; end case; -- concat seems ignore whitespace character. workaround use -- concat_ws whitespace separator when letter whitespace. set translit = concat_ws(if(letter = ' ', ' ', ''), translit, letter); set pos = pos + 1; end while; return translit; end$$ delimiter ; p.s. asking on chat test scenario. need @ least 1 table 2x varchars(255) columns. , sentence book or text, commas, dots, brackets, other identificational signs , etc. results numbers, words, letters , single dashes has remain. when started result lower case word spaces.
Comments
Post a Comment