Answers.Body: <p>The answer at <a href="https://stackoverflow.com/a/17942691/8332458">https://stackoverflow.com/a/17942691/8332458</a> works you just need to have as many &quot;n&quot;s as the maximum number of possible keywords. Below there are 5 but just add more if you need more, e.g. add <code>union all select 6</code>. I'd also just slightly change it to add a <code>WHERE</code> to remove the extra blanks it creates because civi stores the ascii 1 as bookends, like so:</p>
  <pre><code>select
    civicrm_value_XXX.id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(civicrm_value_XXX.keywords_56, CHAR(1), numbers.n), CHAR(1), -1) singlevalue
  from
    (select 1 n union all
     select 2 union all select 3 union all
     select 4 union all select 5) numbers INNER JOIN civicrm_value_XXX
    on CHAR_LENGTH(civicrm_value_XXX.keywords_56)
       -CHAR_LENGTH(REPLACE(civicrm_value_XXX.keywords_56, CHAR(1), ''))&gt;=numbers.n-1
  WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(civicrm_value_XXX.keywords_56, CHAR(1), numbers.n), CHAR(1), -1) &lt;&gt; ''
  order by
    id, n
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-23T14:40:02.363Z
Answers.LastActivityDate: 2023-02-23T14:40:02.363Z
Answers.UserDisplayName: Demerit
Body: <p>Using Drupal 8.9.20 and CiviCRM 5.39.1 (on Ubuntu 18.04 LTS).
  I need to write a query in MySQL to retrieve the values of an optiongroup.</p>
  <p>The OptionGroup is used to assign keywords to a contact record and the number of keywords is different for each record.</p>
  <p>There are some examples on StackOverflow for how to create a function to split the string but most / all of them require an &quot;index&quot; parameter to indicate which substring you want to pull out after the ASCII 001 character (that Civi uses to separate the values), but I can't find anything that would return all of them.
  MySQL returns something like:</p>
  <pre><code>------------------
  + keywords_56    +
  ------------------ 
  - 63210013056479 -
  ------------------
  </code></pre>
  <p>which in reality is 632 100 130 564 79</p>
  <p>Thanks in advance
  A.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-02-23T00:53:12.923Z
Comments.Text: What are you trying to have returned from the SQL? Are you looking for the query result to be `632 100 130 564 79`?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-02-23T11:27:17.410Z
Comments.Text: Hi Demerit, this will be a subquerry of a bigger one. These number correspond with desciptions on another table (where the name of the keyword is stored). I need to build a report that displays the title of the publication, the authors, the abstract, the keywords etc. In CiviCRM DB table naming I am trying to pull the values out of the customfield (above) and match them to civicrm_option_value table where field value = 632 (for example). So I guess have a return in rows so I can do a join or some other match. Does this help?
Comments.UserDisplayName: ArisK
Tags: <mysql><drupal-8>
Title: MySQL OptionGroup values, retrieve in SELECT statement
ThreadText: # MySQL OptionGroup values, retrieve in SELECT statement
  
  (Asked by: username_0 on 2023-02-22)
  
  Using Drupal 8.9.20 and CiviCRM 5.39.1 (on Ubuntu 18.04 LTS).
  I need to write a query in MySQL to retrieve the values of an optiongroup.
  The OptionGroup is used to assign keywords to a contact record and the number of keywords is different for each record.
  There are some examples on StackOverflow for how to create a function to split the string but most / all of them require an "index" parameter to indicate which substring you want to pull out after the ASCII 001 character (that Civi uses to separate the values), but I can't find anything that would return all of them.
  MySQL returns something like:
  ------------------
  + keywords_56    +
  ------------------ 
  - 63210013056479 -
  ------------------
  
  which in reality is 632 100 130 564 79
  Thanks in advance
  A.
  
  **Tags:** mysql, drupal-8
  
  > **Comments:**
  > **username_1**: What are you trying to have returned from the SQL? Are you looking for the query result to be `632 100 130 564 79`?  
  > **username_0**: Hi username_1, this will be a subquerry of a bigger one. These number correspond with desciptions on another table (where the name of the keyword is stored). I need to build a report that displays the title of the publication, the authors, the abstract, the keywords etc. In CiviCRM DB table naming I am trying to pull the values out of the customfield (above) and match them to civicrm_option_value table where field value = 632 (for example). So I guess have a return in rows so I can do a join or some other match. Does this help?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-02-23. Score: 1
  
  The answer at https://stackoverflow.com/a/17942691/8332458 works you just need to have as many "n"s as the maximum number of possible keywords. Below there are 5 but just add more if you need more, e.g. add union all select 6. I'd also just slightly change it to add a WHERE to remove the extra blanks it creates because civi stores the ascii 1 as bookends, like so:
  select
    civicrm_value_XXX.id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(civicrm_value_XXX.keywords_56, CHAR(1), numbers.n), CHAR(1), -1) singlevalue
  from
    (select 1 n union all
     select 2 union all select 3 union all
     select 4 union all select 5) numbers INNER JOIN civicrm_value_XXX
    on CHAR_LENGTH(civicrm_value_XXX.keywords_56)
       -CHAR_LENGTH(REPLACE(civicrm_value_XXX.keywords_56, CHAR(1), ''))>=numbers.n-1
  WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(civicrm_value_XXX.keywords_56, CHAR(1), numbers.n), CHAR(1), -1) <> ''
  order by
    id, n
  
  
  ---
  
  