How can I get values from mysql db, replace some text from the values, and then get the averate of the numeric values?

Question

So I have a column in mysql db that has values such as '34343|dollar' and '2343|dollar'. I wish to get all the column values, then remove the '|dollar' string and get the average of the numbers. How can I achieve this?

And this, boys and girls, is why we don't violate first normal form of relational databases. — Mike
– Mike, Commented May 12, 2016 at 19:27
Possible duplicate of Mysql, storing multiple value in single column from another table — Mike
– Mike, Commented May 12, 2016 at 19:27
See also: stackoverflow.com/questions/1258743/normalization-in-mysql — Mike
– Mike, Commented May 12, 2016 at 19:33

orlevii · Accepted Answer · 2016-05-12 19:38:05Z

0

Something like this:

select AVG(CONVERT(REPLACE(col,'|dollar',''), SIGNED INTEGER)) from my_table;

'col' is your column that has the values as you mentioned.

answered May 12, 2016 at 19:38

orlevii

5074 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pepster Over a year ago

thanks, after that how do I get the result to echo it? seems that it is an object and not a string. thanks.

Collectives™ on Stack Overflow

How can I get values from mysql db, replace some text from the values, and then get the averate of the numeric values?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related