I\u2019m working on my words database using phpMyAdmin and thought others might benefit from some of the search expressions.<\/p>\n
Find words that start with ‘Wr’ or ‘wr’ and the CVC category is not null.
\n
\nSELECT * FROM `words` WHERE `word` REGEXP '^[Ww]r[a-z]*' \nAND `CVC` IS NOT NULL\n<\/code><\/pre><\/p>\nThis gives me words like, Rhoda, Rhone, rhyme because I\u2019m asking to start at the beginning of the word ‘^’ then look for either a capital or lowercase w ‘[Ww] followed by an ‘r’ and then any letter ‘[a-z]’ that occurs zero or more times ‘*’. I\u2019m further restricting the search to words that have an entry in the CVC category. In phpMyAdmin you can paste this into the SQL editing field or just use the REGEX in the word field and set it to REGEXP.<\/p>\n
Here\u2019s one looking for all the words that end in the letters mb and the CVC category is not null.
\n
\nSELECT * FROM `words` WHERE `word` REGEXP 'mb$' \nAND `CVC` IS NOT NULL\n<\/code><\/pre><\/p>\nThe ‘$’ means the end of the word.<\/p>\n
This search yields words like climb, plumb, thumb.<\/p>\n
This search looks for words like gnome and gnat. If you want you can leave out the [a-z]* and you\u2019ll get the same result.
\n
\nSELECT * FROM `words_for_slps` WHERE `word` REGEXP '^[Gg]n[a-z]*' \nAND `CVC` IS NOT NULL\n<\/code><\/pre><\/p>\nThis one\u2019s a little more complicated, I\u2019m looking for words like tongue and dengue but don\u2019t know if there are words with gue in the middle\u2014like tongues.
\n
\nSELECT * FROM `words_for_slps` WHERE `word` REGEXP '^[a-zA-z]*ngue[a-z]*' \nAND `CVC` IS NOT NULL\n<\/code><\/pre><\/p>\nYou can’t do OR queries in phpMyAdmin interface everything is an AND. But if you do a query, then edit the SQL it is easy to make the query into an OR.<\/p>\n
This is the original query to find words where the grade and level are NULL.<\/p>\n
\nSELECT * FROM `words_for_slps` WHERE `level` IS NULL AND `grade` IS NULL \nAND `CVC` IS NOT NULL\n<\/code><\/pre><\/p>\nAnd here is the same code where all I changed is the AND to OR and just to be sure I\u2019m getting the right results, I grouped the OR with parentheses. (I can never the default rules for the different languages so I always use parentheses so I\u2019m sure I get what I want.)
\n
\nSELECT * FROM `words_for_slps` WHERE (`level` IS NULL OR `grade` IS NULL) \nAND `CVC` IS NOT NULL\n<\/code><\/pre><\/p>\nI like this result but want to look deal with the shorter words first and then work my way up to the harder ones. I also want to skip the words that do not follow the rules (DNFR). Use <> for not equal.<\/p>\n
\nSELECT * FROM `words_for_slps` WHERE (`level` IS NULL OR `grade` IS NULL) \nAND `CVC` IS NOT NULL AND `CVC` <> 'DNFR' ORDER BY LENGTH(word)\n<\/code><\/pre><\/p>\nHere’s the code I use to find all of the conosonants
\n
\n([bcdfghjklmnpqrstvwxz]|ch|sh|zh|th|wh|ng|qu|wh|tch|ph)\n<\/code><\/pre><\/p>\nAnd this finds all the vowels.
\n
\n(a|e|i|o|u|aw|ow|ie|ae|oe|oy|oo|uu|y)\n<\/code><\/pre><\/p>\nAnd this finds all of the CVCCV words. Note the {2} that finds exactly two occurrences of the previous search string.
\n
\nSELECT * FROM `words_for_slps` WHERE `word`\n^([bcdfghjklmnpqrstvwxz]|ch|sh|zh|th|wh|ng|qu|wh|tch|ph)\n(a|e|i|o|u|aw|ow|ie|ae|oe|oy|oo|uu|y)\n([bcdfghjklmnpqrstvwxz]|ch|sh|zh|th|wh|ng|qu|wh|tch|ph){2}\n(a|e|i|o|u|aw|ow|ie|ae|oe|oy|oo|uu|y)$\n<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"I\u2019m working on my words database using phpMyAdmin and thought others might benefit from some of the search expressions. Find words that start with ‘Wr’ or ‘wr’ and the CVC category is not null. SELECT * FROM `words` WHERE `word` REGEXP '^[Ww]r[a-z]*' AND `CVC` IS NOT NULL This gives me words like, Rhoda, Rhone, rhyme because … Continue reading REGEX and MySQL<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-1223","post","type-post","status-publish","format-standard","hentry","category-coding"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1223","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1223"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1223\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1223"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1223"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}