{"id":1324,"date":"2012-04-02T07:45:58","date_gmt":"2012-04-02T14:45:58","guid":{"rendered":"http:\/\/www.wellgolly.com\/?p=1324"},"modified":"2012-04-04T15:58:53","modified_gmt":"2012-04-04T22:58:53","slug":"concatenating-fields-in-mysql","status":"publish","type":"post","link":"https:\/\/www.wellgolly.com\/?p=1324","title":{"rendered":"Concatenating Fields in MySql"},"content":{"rendered":"

I’m working on an iPhone\/iPad product that needs to select a word based on the phonemes in it. I have about 40 phonemes that are stored in a MySql table as booleans. I didn’t feel like adding 40 attributes to my datamodel and then I’d have to write 40 conditionals to figure out whether a word contained the phoneme. Fortunately, MySql has a CONCAT function String Operator<\/a> that I could use to concatenate all the fields into one. Now I can use REGEX expressions to query one field to determine if it is on or off for a specific word.<\/p>\n

My first attempt to understand the operator was to just copy the example.
\n

\nUPDATE `ArticIV_Phonemes` SET `Concatenated_phonemes` = CONCAT(`R`,`VocR`)\n<\/code><\/pre><\/p>\n
This isn\u2019t what I wanted since it treats the stuff between the `s as literals. The result is RVocR. Not the values of the fields. Removing the `s works.
\n
\nUPDATE `ArticIV_Phonemes` SET `Concatenated_phonemes` = CONCAT(R,VocR)\n<\/code><\/pre><\/p>\n
Now I get, 01, 10, 00, etc. which is what I want.<\/p>\n
One minor complication. One of the phonemes is \\or\\ so naturally I used or<\/b> as it\u2019s name. My treats this name as a conditional and the CONCAT operation fails. I also used a hyphen in some of the names. My doesn’t like that either. It probably thinks it\u2019s an arithmetic operator.<\/p>\n
 This is what I ended up using.
\n
\nUPDATE `ArticIV_Phonemes` SET `Concatenated_phonemes` = CONCAT(R_Words,Vocalic_R_Words,S_Words,Z_Words,L_Words,Voiced_Th_Words,Voiceless_Th_Words,\nInitial_R, Initial_R_Clusters, Medial_R, Medial_R_Clusters, \nVocR_aer, VocR_ar, VocR_eer, VocR_er, VocR_ier, VocR_or, VocR_our, VocR_yer, \nInitial_S, Initial_S_Clusters, Medial_S, Medial_S_Clusters, Final_S, Final_S_Clusters, \nInitial_Z, Initial_Z_Clusters, Medial_Z, Medial_Z_Clusters, Final_Z, Final_Z_Clusters, \nInitial_L, Initial_L_Clusters, Medial_L, Medial_L_Clusters, Final_L, \nFinal_L_Clusters, Pre_vocalic_L, Post_vocalic_L, Pre_and_Post_V_L, \nInitial_Th, Medial_Th, \nInitial_Voiceless_Th, Initial_Voiceless_Th_Clusters,\nMedial_Voiceless_Th, Final_Voiceless_Th, Final_Voiceless_Th_Clusters)\n<\/code><\/pre><\/p>\n
And here are the first few lines.
\n
\n00001000000000000000000000000000010000010000000\n00000000100000000000000000000000000000000000000\n00000000000000000100000100000000000000000000000\n<\/code><\/pre><\/p>\n
When I want to find all of the words that have say, VocR_ier, in them I use this REGEX
\n
\nSELECT *  FROM `ArticIV_Phonemes` WHERE `Concatenated_phonemes` REGEXP '[01]{9}1[01]{34}'\n<\/code><\/pre>
\nBasically this says, look for 9 occurrences of either a 0 or a 1. Then a 1. Then the rest of the string can be either 0 or 1. Note: you must put the final part in. The first part of the REGEX looks for a string of 9 0’s or 1’s followed by a 1. It doesn’t care where they are in the string. They could be the last 10 digits or the first.<\/p>\n
One way to get around that is to tell the REGEX to start at the beginning of the string and look for the pattern.
\nNote the ^ in this example.
\n
\nSELECT *  FROM `ArticIV_Phonemes` WHERE `Concatenated_phonemes` REGEXP '^[01]{9}1'\n<\/code><\/pre><\/p>\n
Both examples give the same result,
\n
\n00000000110000000000000000000000000000000000000\n00000000110000000000000000000000100000000000000\n00000000100000000000000000000000010000010000000\n<\/code><\/pre><\/p>\n
Here’s what I used in a different database.
\nUPDATE `OldArtic_Categories` SET `Concatenated_Category` = CONCAT(A, B, M, D, C, E, F, G, H, I, K, L, O, P, Q, R, S, T, U, V, W, Y, Z, N)<\/p>\n","protected":false},"excerpt":{"rendered":"
I’m working on an iPhone\/iPad product that needs to select a word based on the phonemes in it. I have about 40 phonemes that are stored in a MySql table as booleans. I didn’t feel like adding 40 attributes to my datamodel and then I’d have to write 40 conditionals to figure out whether a … Continue reading Concatenating Fields in 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-1324","post","type-post","status-publish","format-standard","hentry","category-coding"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1324","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=1324"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1324\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}