How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column:

TABLE 1: word_scores

word       score
this       3
is         4
a          5
test       1
another    0
sentence   8

TABLE 2: sentences (score column is calculated from TABLE 1)

sentence           score
this is a test     13
another sentence   8
this is            7

Now I need to

1. fetch each sentence into PHP,
2. then split the string to words,
3. then catch the score for each word,
4. then calculate the sum of word_scores

Is it possible to calculate the sentence score within mysql?

Bascially yes, there's a way to do that.

Here's a select statement that gets you each sentence with it's score

SELECT
PARSED_SENTENCES.sentence, SUM(COALESCE(WordScores.score, 0)) score
FROM (
     SELECT sentence,
     SUBSTRING(
          sentence
          FROM CASE
               WHEN INDEX_TABLE.POS = 1 THEN 1
               ELSE INDEX_TABLE.POS + 1
               END
          FOR CASE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              WHEN 0 THEN CHARACTER_LENGTH(sentence) + 1
              ELSE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              END
              - CASE
                WHEN INDEX_TABLE.POS = 1 THEN 1
                ELSE INDEX_TABLE.POS + 1
                END
     ) AS word
     FROM SentenceScores
     INNER JOIN (
          SELECT @rownum:=@rownum+1 POS
          FROM (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) a, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) b, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) c, (SELECT @rownum:=0) r
     ) INDEX_TABLE
     ON INDEX_TABLE.POS <= CHAR_LENGTH(SentenceScores.sentence)
     AND (
          INDEX_TABLE.POS = 1
          OR SUBSTRING(SentenceScores.sentence FROM INDEX_TABLE.POS FOR 1) = ' '
     )
) AS PARSED_SENTENCES
LEFT OUTER JOIN WordScores
ON PARSED_SENTENCES.word = WordScores.word
GROUP BY PARSED_SENTENCES.sentence;

You should be able to convert that to an update statement so that you could calculate the scores and apply them to the table at the same time.

Per the comment discussion below, if you wanted to replace the numbers-table subquery with a static numbers table you could replace this part of the query:

INNER JOIN (
      SELECT @rownum:=@rownum+1 POS
      FROM (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) a, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) b, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) c, (SELECT @rownum:=0) r
 ) INDEX_TABLE

with just:

INNER JOIN INDEX_TABLE

where your numbers table and number column are assumed to have the same names as in the subquery, i.e. INDEX_TABLE and POS

StackExchange Database Administrators Q#30234, answer score: 5