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
– Gordon Linoff, Commented Apr 2, 2016 at 21:04
@GordonLinoff I agree. I got the data in this format so now I have to deal with those. — Wistar
– Wistar, Commented Apr 2, 2016 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
– Loufylouf, Commented Apr 2, 2016 at 21:09
@Loufylouf, yes I might end up just doing this: stackoverflow.com/questions/3232511/… I was wondering if there was a way to do that in MySQL — Wistar
– Wistar, Commented Apr 2, 2016 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
– Loufylouf, Commented Apr 2, 2016 at 21:15

Gordon Linoff · Accepted Answer · 2016-04-02 21:16:48Z

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.

BlackVikingPro · Accepted Answer · 2016-04-02 21:09:13Z

-1

SELECT SUM(column_name) FROM table_name;

or

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

I hope this helps!

answered Apr 2, 2016 at 21:09

BlackVikingPro

271 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQL SUM number within string

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related