SET @foo := '6:375:00';
SELECT SUBSTRING_INDEX(@foo,':',1) AS p1
, SUBSTRING_INDEX(SUBSTRING_INDEX(@foo,':',2),':',-1) AS p2
, SUBSTRING_INDEX(@foo,':',-1) AS p3
SELECT v.foo
, CONCAT( RIGHT(CONCAT('00', v.p1 + 0 ),2)
, RIGHT(CONCAT('00', (v.p2 + 0) MOD 60),2)
, RIGHT(CONCAT('00', v.p3 +0 ),2)
) AS bar
FROM (
SELECT @foo AS foo
, SUBSTRING_INDEX(@foo,':',1) AS p1
, SUBSTRING_INDEX(SUBSTRING_INDEX(@foo,':',2),':',-1) AS p2
, SUBSTRING_INDEX(@foo,':',-1) AS p3
) v
要对表的列使用这些操作,请替换 @foo 使用有效的列名,并添加 FROM 条款。像这样:
SELECT v.foo
, CONCAT( RIGHT(CONCAT('00', v.p1 + 0 ),2)
, RIGHT(CONCAT('00', (v.p2 + 0) MOD 60),2)
, RIGHT(CONCAT('00', v.p3 +0 ),2)
) AS bar
FROM (
SELECT t.foo AS foo
, SUBSTRING_INDEX(t.foo,':',1) AS p1
, SUBSTRING_INDEX(SUBSTRING_INDEX(t.foo,':',2),':',-1) AS p2
, SUBSTRING_INDEX(t.foo,':',-1) AS p3
FROM my_table t
) v
转换失败时,源 foo 值不包含两个冒号字符。 根本不清楚我们要对转换后的值做什么(如果这是一个“时间”值,我倾向于将该值转换为 TIME 数据类型,而不是字符串。
1条答案
按热度按时间btqmn9zl1#
转换的规则还不清楚。看起来我们想实现某种转换
看来需要把原来的绳子分成三部分。
sql不是解析字符串的最佳工具,但是我们可以一起破解一些东西。
假设所有值都包含两个冒号字符,我们可以这样做(在这里使用用户定义的变量代替列名)
我们得到
看来我们需要做些算术。我们是在第一个冒号(p1)之前的部分,并用它作为“小时”吗?
第二部分(冒号之间)可能是午夜后的几分钟。我们可以做一个模60运算,得到余数,然后用它作为分钟数(我们还可以使用整数除以60的结果,并将其用作“小时”。
我们可以给每个部分加零,把它们转换成数字,然后我们可以把每个部分格式化成一个由两个数字组成的带前导零的字符串。然后将这些部分连接回一个字符串。
要对表的列使用这些操作,请替换
@foo
使用有效的列名,并添加FROM
条款。像这样:转换失败时,源
foo
值不包含两个冒号字符。根本不清楚我们要对转换后的值做什么(如果这是一个“时间”值,我倾向于将该值转换为
TIME
数据类型,而不是字符串。