Jump to content

Wikipedia:WikiProject Red Link Recovery/Link matching script/Initialisation

From Wikipedia, the free encyclopedia

This requires mysql 5.0.3 or newer.

This converts:

"Mickey Fred Mouse" -> "Mickey F Mouse"
"Mickey F. Mouse" -> "Mickey F Mouse"
"Mickey Fred Ginger Mouse" -> "Mickey F G Mouse"



DROP FUNCTION initialise;

DELIMITER //

CREATE FUNCTION initialise( text varchar(255) ) RETURNS varchar(255)
BEGIN
  DECLARE l_ret varchar(255);
  DECLARE l_sp1, l_sp2 int;
   
  SET l_ret = text;
  SET l_sp1 = locate( '_', l_ret );
  SET l_sp2 = locate( '_', l_ret, l_sp1 + 1 );
   
  WHILE l_sp2 > 0 DO
   
    SET l_ret = concat( left( l_ret, l_sp1 ), mid( l_ret, l_sp1 + 1, 1 ), mid( l_ret, l_sp2 ) );
   
    SET l_sp1 = locate( '_', l_ret, l_sp1 + 1 );
    SET l_sp2 = locate( '_', l_ret, l_sp1 + 1 );

  END WHILE;  
   
  RETURN l_ret;
  
END;
//

DELIMITER ;

UPDATE crushed_art
SET title = initialise( title )
WHERE title REGEXP '^[[:upper:]][[:alpha:]]*_[[:upper:]].+_[[:upper:]][[:alpha:]]*$'
AND title NOT LIKE '%\_\_%';

UPDATE crushed_links
SET link = initialise( link )
WHERE link REGEXP '^[[:upper:]][[:alpha:]]*_[[:upper:]].+_[[:upper:]][[:alpha:]]*$'
AND link NOT LIKE '%\_\_%';