MySQL:如何使用“-"分割文本和数字 [英] MySQL : how to split text and number with "-"

问题描述

我想重写一些用文本和数字之间用-"分隔的用户ID.例如:

I would like to rewrite a number of user id which is splited by "-" between text and number. For example:

• KT-12345
• BA-98765
• CC-98765
• ITA-87987

从大量包含文本和数字的数据中提取.例如:

From a huge amount of data which is mixed up with text and number. For example:

• KT98798
• CC94788
• BB87600

所以问题是:我想将用户ID从第二个示例转换为第一个示例.如何在MySQL中实现它.请提出建议.

So the question is : I would like to make the user id from the 2nd examples into the first one. How to achieve it in MySQL. Please suggest.

推荐答案

SELECT
CASE
WHEN floor(substr(name, 3,1)) > 0
THEN CONCAT_WS('-', SUBSTRING(name, 1, 2), SUBSTRING(name, 3, LENGTH(name)))
ELSE CONCAT_WS('-', SUBSTRING(name, 1, 3), SUBSTRING(name, 4, LENGTH(name)))
END AS new_name
FROM test

这篇关于MySQL:如何使用“-"分割文本和数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！