在生产中遇到过字段分割的需要:将日期时间字段【20221103 12:15】的日期时间,分割成两个字段【日期20221103、时间12:15】。
用了下面的写法可以实现,具体的意思忘记了,有时间再补上,或者看明白的小伙伴欢迎分享在评论区。
select path
, substring_index(path
, '|'
, 1) as first_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 1
then substring_index(substring_index(path
, '|'
, 2)
, '|'
, -1) end as second_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 2
then substring_index(substring_index(path
, '|'
, 3)
, '|'
, -1) end as third_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 3
then substring_index(substring_index(path
, '|'
, 4)
, '|'
, -1) end as fourth_achievement_id
, case
when (LENGTH(path) - LENGTH(REPLACE (path
, '|'
, ''))) >= 4
then substring_index(substring_index(path
, '|'
, 5)
, '|'
, -1) end as fifth_achievement_id
from student_achievement;