It can be tricky to update records in a MySQL database using regular expressions because the stock install doesn’t support replacement strings. Occasionally, you get lucky and your data can be manipulated with a combination of REGEXP and strings. This is one case.<\/p>\n
My phonemes field is constructed of phonemes separated by spaces. e.g.
\n
\nb \u025b,i n\nbl \u00e6,\u0259 n t\u0283\nbr \u00e6,\u0259 n t \u00e6,\u0259\n<\/code><\/pre><\/p>\nI want to update the Initial and Final sounds fields with the sounds IF they are consonants but not if they are vowels. My REGEXP selects all of the consonants\u2014including sh (\u0283) and th (\u03b8 or \u00f0) at the beginning ‘^’ or end ‘$’ of the phoneme. Then I split the phonemes field by the first or last space and place that value into the Initial or Final sound field. Along the way I convert it to lower case. Also note that I want initial the initial w to count as a consonant, but not if it occurs at the end\u2014but my phoneme rules should have caught most of those.<\/p>\n
\nUPDATE `words_for_slps` \nSET `I` = LOWER(SUBSTRING_INDEX(phonemes,' ', 1)) \nWHERE `phonemes` REGEXP '^[\u0283\u03b8\u00f0bcdfghjklmnpqrstvwxz]+'\n<\/code><\/pre><\/p>\n\nUPDATE `words_for_slps` \nSET `F` = LOWER(SUBSTRING_INDEX(phonemes,' ', -1)) \nWHERE `phonemes` REGEXP '[\u014b\u0283\u03b8\u00f0bcdfghjklmnpqrstvxz]$'\n<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"It can be tricky to update records in a MySQL database using regular expressions because the stock install doesn’t support replacement strings. Occasionally, you get lucky and your data can be manipulated with a combination of REGEXP and strings. This is one case. My phonemes field is constructed of phonemes separated by spaces. e.g. b … Continue reading More MySQL goodness<\/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-1239","post","type-post","status-publish","format-standard","hentry","category-coding"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1239","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=1239"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1239\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}