mysql:将分数字符串转换为数字 [英] mysql : turn fraction strings into number

问题描述

我有像...3/4"和5/9"这样的字符串，还有一些像...1/2 km"和3/4 degree"存储在mysql列中.

I have strings like... "3/4" and "5/9" and some like... "1/2 km" and "3/4 degree" stored in mysql columns.

我想将它们转换成数字.在第一种情况下，3/4 ==> .75.
在更复杂的第二种情况下，去掉像km"和degree"这样的单位1/2 公里"==> 0.5.

I would like to convert them into numbers. In first case, 3/4 ==> .75.
In more complicated second case, strip off units like "km" and "degree" so "1/2 km" ==> 0.5.

推荐答案

我认为这不是你应该在查询中做的事情，而是在存储时计算它并将计算值保存在它旁边的表中文本值.

I think this isn't something you should do in a query, but rather calculate it when it is stored and save the calculated value in the table next to its text value.

但如果你愿意，你可以使用一些字符串函数来分割值并自己做数学:

But if you want to, you can use some string functions to slice up the value and the do the math yourself:

select
  x.Multiplier / x.Divider as Result
from
    (select
      cast( substr( t.String, 
                    1, 
                    locate('/', t.String) - 1) 
            as decimal) 
        as Multiplier,
      cast( substr( t.String, 
                    locate('/', t.String) + 1, 
                    locate( ' ', 
                            concat(t.String, ' '))) 
            as decimal) 
        as Divider
    from
      YourTable t) x

但是请注意，如果数据无效"，这可能会导致问题.如果显示0/0 km"，则可能会失败，如果包含此处无数据"，则也可能会失败.

Note however, that this may cause trouble if the data is 'invalid'. If it says '0/0 km', it may fail, if it contains 'no data here' it may fail as well.

这篇关于mysql:将分数字符串转换为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！