0

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?

pepster
  • 41
  • 4
  • 1
    And this, boys and girls, is why we don't violate first normal form of relational databases. – Mike May 12 '16 at 19:27
  • 1
    Possible duplicate of [Mysql, storing multiple value in single column from another table](http://stackoverflow.com/questions/4804841/mysql-storing-multiple-value-in-single-column-from-another-table) – Mike May 12 '16 at 19:27
  • See also: http://stackoverflow.com/questions/1258743/normalization-in-mysql – Mike May 12 '16 at 19:33

1 Answers1

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.

orlevii
  • 427
  • 4
  • 10
  • thanks, after that how do I get the result to echo it? seems that it is an object and not a string. thanks. – pepster May 12 '16 at 21:11