MySQL将高度格式转换为厘米 [英] MySQL convert height format to centimeters

问题描述

我想将英尺和英寸转换为厘米格式

I want to convert feet and inches to centimeters format

我的数据库中的格式是:

Format in my DB is:

4'6"(4 英尺 6 英寸)

4'6" (4 feet, 6 inches)

厘米换算公式

4*30.48 = 121.92 (convert feet to centimeters = multiply by 30.48)
6*2.54 = 15.24 (convert inches to centimeters   = multiply by 2.54)
So Result = 121.92 + 15.24 = 137.16 cm

例如:

实际表:英寸

SELECT * FROM inches

id height
1  4'6"
2  4'7"
3  5'8"
4  5'9"

当我执行 SQL 查询时，我期望以下结果为厘米

I expect the following result as centimeters when I do SQL query

id height
1  137.16
2  139.7
3  172.72
4  175.26

提前致谢:)

推荐答案

应用端流程会更好，

然而，

    SELECT 
            (CAST(SUBSTR(height,1, LOCATE("'",height)-1) AS UNSIGNED) * 30.48) + 
            (CAST(SUBSTR(height,   LOCATE("'",height)+1) AS UNSIGNED) * 2.54 )   AS cm
    FROM 
            inches;

这篇关于MySQL将高度格式转换为厘米的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！