add integer from string consisted of character and integer having the same character

Question

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

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
– user3783243, Commented Aug 9, 2018 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
– Forbs, Commented Aug 9, 2018 at 20:28
That's why we should use the 1NF (en.wikipedia.org/wiki/Database_normalization#Normal_forms) — Felippe Duarte
– Felippe Duarte, Commented Aug 9, 2018 at 20:28

Barmar · Accepted Answer · 2018-08-09 20:51:32Z

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.

I've updated the answer to say how to do it with a variable-length prefix.

Collectives™ on Stack Overflow

add integer from string consisted of character and integer having the same character

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related