0

I have a VARCHAR column that contains a bit of text (non-numeric) followed by an integer, e.g. 'foobar: 42'. I would like to get the SUM() of the numbers, but I can't think of a way to do it.

  • If the number was at the beginning, just summing the column would work, but apparently the content has to begin with the number. (It didn't throw an error, but I got 0 as a result.)
  • If I was sure of the number of leading non-numerics I could use SUBSTRING(), but I can't be sure.
  • I thought of using something like LEFT(Column, INSTR(Column, '4')), but I would have to check for any of the ten digits, and I don't see a way to do that.
  • REGEXP doesn't appear to help me because it only does matching, not replacement.

Does someone have a clever idea? Yes, I can do a loop in PHP later if I have to, but there are other complexities involved that would get pretty messy if I had to do it that way.

OsakaWebbie
  • 645
  • 1
  • 7
  • 21
  • Is the `:` constant? `select sum(cast(substring(column, instr(column, ':')+1) as signed)) from table;` – bishop Jun 14 '16 at 15:53
  • @bishop: Possibly - I prefill the form with it, but it could be overwritten. But also, there might (or might not) be a space after the colon. – OsakaWebbie Jun 14 '16 at 16:24
  • I checked the other thread cited by Álvaro and implemented one of its answers (the one that claims to be faster than the most popular one). I wouldn't have considered it an exact duplicate (I sure didn't find it with a lot of searching effort), but it's close enough. Thanks! – OsakaWebbie Jun 14 '16 at 16:57
  • See also `SUBSTRING_INDEX()`. – Rick James Jun 24 '16 at 16:40
  • @Rick: Thanks, but that wouldn't have helped in this situation because the character before the number is not guaranteed to be consistent (might be a space, an ASCII colon, a multibyte colon (the users of this usecase are in Japan), or possibly something else. – OsakaWebbie Jun 25 '16 at 00:53
  • SQL is not the best language for cleansing data. That task should be done in the client code _before_ touching the database. – Rick James Jun 25 '16 at 05:22
  • I'm not cleansing data - the user can put anything they want in the field, and that's fine. I'm just giving them a chart of summarized results of that data based on some very complicated criteria from joins, and although I could have looped through the data in PHP and kept a running total, it was much easier to do the aggregate in MySQL once I had a way to get the number out of it. As I said above, I have implemented a solution - a UDF that was suggested in http://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare by user "wally". – OsakaWebbie Jun 25 '16 at 14:01

0 Answers0