drop table if exists temp_20221202;

create table temp_20221202(char_name varchar(1024));

insert into temp_20221202

values ('SWID:123456,XH:S-1234,SCCMJ:测试')

select max(t3.SWID) as SWID

   ,max(t3.XH) as XH 

,max(t3.SCCMJ) as SCCMJ

from (

select COALESCE((case when pre_char='SWID' THEN after_char end),'0') as SWID

,COALESCE((case when pre_char='XH' THEN after_char end),'0') as XH

,COALESCE((case when pre_char='SCCMJ' THEN after_char end),'0') as SCCMJ

from (

select SUBSTRING_INDEX(t.char_name_split,':',1) as pre_char

,SUBSTRING_INDEX(t.char_name_split,':',-1) after_char

from (SELECT a.char_name

    , substring_index(substring_index(a.char_name, ',', b.help_topic_id + 1), ',', - 1) AS char_name_split

FROM temp_20221202 a

INNER JOIN mysql.help_topic b

    ON b.help_topic_id < (length(a.char_name) - length(REPLACE(a.char_name, ',', '')) + 1)) t

) tt 

) t3