mysql - 如何使用 SUBSTRING_INDEX() 从字符串中获取多个值

Question

我有一个表，我在其中提取一些值，一列值可以包含

'FLSD202-D-B-D-AB-C1-NN-A-N-LA-J-NN/UM/H7/SCT'

我想把它们拆分成

FLSD202 AS first column
-D-B-D-AB-C1-NN-A-N-LA-J-NN AS Second column
/UM/H7/SCT AS third column

这是我的脚本，但我无法得到我想要的结果

SELECT 'FLSD202-D-B-D-AB-C1-NN-A-N-LA-J-NN/UM/H7/SCT', SUBSTRING_INDEX(SUBSTRING_INDEX('FLXA202-D-B-D-AB-C1-NN-A-N-LA-J-NN/UM/H7/SCT', '-',1), '-', -1) FirstColumn, 
CONCAT('-', SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('FLSD202-D-B-D-AB-C1-NN-A-N-LA-J-NN/UM/H7/SCT', '/',1), '/', -1), '-',-1)) SecondColumn,
CONCAT('/', SUBSTRING_INDEX(SUBSTRING_INDEX('FLSD202-D-B-D-AB-C1-NN-A-N-LA-J-NN/UM/H7/SCT', '/',3), '/', -1)) ThirdColumn

我想将其用于所有值，而不仅仅是一个字段..

'-'(破折号)是动态的，'/'(斜杠)是动态的，例如:SSS145-SS3/POP、KEEE-CDE0/NO/SA、WXAE-C-D/E/G、SAD-SEU-SFX/OPS

谢谢

Answer 1

我认为下面的代码将帮助您完成您的要求。

SET @a = 'FLSD202-D-B-D-AB-C1-NN-A-N-LA-J-NN/UM/H7/SCT';
SET @dash_start_position = LOCATE('-',@a); 
SET @slash_start_position = LOCATE('/',@a);
SELECT SUBSTRING(@a, 1, @dash_start_position-1) as First, IF(@slash_start_position IS NOT NULL AND @slash_start_position > 0, SUBSTRING(@a, @dash_start_position, @slash_start_position - @dash_start_position), SUBSTRING(@a, @dash_start_position)) as Second,SUBSTRING(@a, @slash_start_position) as Third;