MySQL SUM number within string

Question

I have a movies table

SELECT * FROM movies

+-----------------------------------+---------------------------------------------------------+
|            MovieTitle             |                         Awards                          |
+-----------------------------------+---------------------------------------------------------+
| Rocky 20                          | Nominated for 1 Oscar. Another 37 wins & 46 nominations |
| Die Hard 51                       | Won 1 Oscar. Another 5 wins & 19 nominations.           |
| Killer tomatoes                   | 9 nominations.                                          |
+-----------------------------------+---------------------------------------------------------+

I would like to be able to SUM all the numbers of award/nominations in Awards such as to have something like this:

    +-----------------------------------+---------------------------------------------------------+-------+
    |            MovieTitle             |                         Awards                          | Total |
    +-----------------------------------+---------------------------------------------------------+-------+
    | Rocky 20                          | Nominated for 1 Oscar. Another 37 wins & 46 nominations |    84 |
    | Die Hard 51                       | Won 1 Oscar. Another 5 wins & 19 nominations.           |    25 |
    | Killer tomatoes                   | 9 nominations.                                          |     9 |
    +-----------------------------------+---------------------------------------------------------+-------+

Any suggestions on how I could achieve that with only MySQL?

You should really store the awards information in a different format. What you want to do is not what relational databases are designed to do. — Gordon Linoff, Apr 02 '16 at 21:04
@GordonLinoff I agree. I got the data in this format so now I have to deal with those. — Wistar, Apr 02 '16 at 21:07
Can't you change of format ? Using a PHP script to process this and insert the data in the new format would not that complicated. — Loufylouf, Apr 02 '16 at 21:09
@Loufylouf, yes I might end up just doing this: http://stackoverflow.com/questions/3232511/get-the-sum-of-digits-in-php I was wondering if there was a way to do that in MySQL — Wistar, Apr 02 '16 at 21:10
I don't think so, string processing isn't really what databases are for. For example, the way I see it, you would not a function to split the string into an array, filter this array to remove not numbers, then do the sum of numbers contained in this array. — Loufylouf, Apr 02 '16 at 21:15

score 2 · Accepted Answer · answered Apr 02 '16 at 21:16

Your strings are all looking for the number before "nominations", "wins" and "Oscars". This simplifies the problem.

You can get the value that you want using string manipulation. Here is one solution:

select ((case when awards like '% nominations%'
              then substring_index(substring_index(awards, ' nominations', 1), ' ', -1) + 0
              else 0
         end) +
        (case when awards like '% wins%'
              then substring_index(substring_index(awards, ' wins', 1), ' ', -1) + 0
              else 0
         end) +
        (case when awards like '% Oscar%'
              then substring_index(substring_index(awards, ' Oscar', 1), ' ', -1) + 0
              else 0
         end)
        ) as TotalMentions

I can appreciate having to deal with poorly formed data in a relational database. In this case, the strings are pretty well formed, so the solution is not that unreasonable.

score -1 · Answer 2 · answered Apr 02 '16 at 21:09

-1

SELECT SUM(column_name) FROM table_name;

or

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

I hope this helps!

answered Apr 02 '16 at 21:09

BlackVikingPro

27
1
2
8

MySQL SUM number within string

2 Answers2