-1

I have this mysql table.

name   |  column
bob11  |  value
bob22  |  value
red12  |  value
bam22  |  value

What i want to do is to add the numbers 11 and 22 because the name bob is just the same. I want to output it this way together with the other names.

name   |  column
bob33  |  value
red12  |  value
bam22  |  value

I know already how to get the column with the same value by this code.

"SELECT name, COUNT(*) as count FROM table GROUP BY name HAVING COUNT(*) > 1"

But i don't know how to do the operation. Any idea would be much appreciated. thanks

kristyan markes
  • 37
  • 1
  • 2
  • 8
  • I don't think you can do that in SQL alone. You could strip the integer and the name in PHP, add the integers, and then append back to the name. – user3783243 Aug 09 '18 at 20:28
  • learn the `substr` command. Sorry but your query is a little general and that's the best I can give you – Forbs Aug 09 '18 at 20:28
  • That's why we should use the 1NF (https://en.wikipedia.org/wiki/Database_normalization#Normal_forms) – Felippe Duarte Aug 09 '18 at 20:28

1 Answers1

0

If the part before the number is always 3 characters long, you can use LEFT() and SUBSTR() to extract the prefix and number, then use CONCAT() and SUM() to combine them.

SELECT CONCAT(LEFT(name, 3), SUM(SUBSTR(name, 4))) AS name, `column`
FROM yourTable
GROUP BY LEFT(name, 3);

DEMO

If not, you'll need to write a function that returns the position of the numeric suffix, and use that instead of hard-coding 3.

SELECT CONCAT(LEFT(name, number_pos(name)-1), SUM(SUBSTR(name, number_pos(name)))) AS name, `column`
FROM yourTable
GROUP BY LEFT(name, number_pos(name)-1)

See Finding number position in string for ways to find the position of the number.

Barmar
  • 741,623
  • 53
  • 500
  • 612