How to identify consonant/vowel pattern matches in MySQL? Evening all,
I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my thinking is something structured along these lines with about 300k rows:

CREATE TABLE words(
 `value` VARCHAR(100),
 `pattern` VARCHAR(100)
);


To populate a small fraction of the contents:

INSERT INTO words(`value`) VALUES
  ('aardvark'),
  ('aardvarks'),
  ('aardwolf'),
  ('aardwolves'),
  #...
  ('mortal'),
  ('mortals'),
  #...
  ('posted'),
  ('posteen'),
  #...
  ('zymotoxic'),
  ('zymurgies');


The following query should return "mortal" and "posted":

SELECT `value` FROM words WHERE `pattern` = '101101';


because '1' represents a consonant and '0' a vowel. I'm aware that MySQL doesn't have a regexp_replace equivalent, so I'm wondering what's the best way to populate the pattern field? I'm assuming it's not the deplorably slow:

UPDATE words SET `pattern` = REPLACE(`value`, 'a', '0');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'b', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'c', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'd', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'e', '0');
#...
UPDATE words SET `pattern` = REPLACE(`pattern`, 'z', '1');


Currently trying a stored procedure with a cursor looping through the words table and updating each pattern one by one but, perhaps unsurprisingly, that's no quicker. Am I missing something really obvious?

Thanks in advance for any help you're able to give me.

Edit: added the stored procedure approach. This is quicker but not by much (0.7 seconds per thousand rows, which adds up) but it gets the job done.

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `set_word_pattern`()
BEGIN
  DECLARE l_last_row INT DEFAULT 0;
  DECLARE temp_word VARCHAR(100);
  #DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE c_traverse_words CURSOR FOR SELECT `value` FROM words WHERE pattern = "" LIMIT 20000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;

  OPEN c_traverse_words;
  cursor_loop: LOOP
    FETCH c_traverse_words INTO temp_word;
    IF l_last_row = 1 THEN
      LEAVE cursor_loop;
    END IF;
    SET temp_pattern_word = get_pattern(temp_word);
    UPDATE words SET `pattern` = temp_pattern_word WHERE `value` = temp_word;
  END LOOP cursor_loop;
  CLOSE c_traverse_words;

  SELECT "Done"; #temp_pattern_word;
END


and the function it calls:

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `get_pattern`(new_word VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE i INT;

  SET temp_pattern_word = "";
  SET i = 1;

  WHILE(i &lt;= CHAR_LENGTH(new_word)) DO
    SET temp_char = MID(new_word, i, 1);

    IF temp_char IN ("a", "e", "i", "o", "u") THEN
      SET temp_pattern_word = CONCAT(temp_pattern_word, "0");
    ELSE
      SET temp_pattern_word = CONCAT(temp_pattern_word, "1");
    END IF;
    SET i = i + 1;
  END WHILE;
RETURN temp_pattern_word;
END
To do it in one pass:

UPDATE ...
    SET pattern = 
        REPLACE(
        REPLACE(
        ...
        REPLACE(value
                'a', '0')
                'b', '1')
                ...
                'z', '1');


Doesn't your table need

PRIMARY KEY(value),
INDEX(pattern)


There is probably a way to convert those 0s and 1s into a bit string and store it into a BIGINT UNSIGNED for slightly faster lookup and less disk space.