MySQL将错乱的水果信息，截取展示为品名英文名价格三列展示-CSDN博客

本文链接：https://blog.csdn.net/weixin_47139678/article/details/139531350

将错乱的水果信息，截取展示为品名英文名价格三列展示

id	name
1	苹果Apple7
2	Plum6李子
3	Pineapple8菠萝
4	Mango5芒果
5	龙吐珠5Buddha’sHand
6	Olive9橄榄
7	Raspberry4树莓
8	Apricot5杏子
9	Grapefruit9柚子
10	火龙果Dragonfruit9
11	倒挂金钟Hanging6LobsterClaw
12	巨峰葡萄Concord5Grape
13	Lingonberry5红莓
14	甜橙7SweetOrange
15	金桔8Kumquat
16	金橘5Tangerine
17	玫瑰果6RoseHip
18	硬木仁Brazil7Nut
19	Pomegranate8石榴
20	黑莓Blackberry5
21	Banana5香蕉
22	橙子Orange7
23	Grape7葡萄
24	草莓4Strawberry
25	Watermelon6西瓜
26	梨9Pear
27	Peach9桃子
28	樱桃Cherry6
29	Lemon9柠檬
30	木瓜Papaya9
31	杨梅Bayberry7
32	Pomelo4柚子
33	榴莲Durian8
34	荔枝Lychee8
35	Blueberry5蓝莓
36	樱桃番茄Cherry9Tomato
37	Rambutan6红毛丹
38	Avocado5牛油果
39	椰子5Coconut
40	柚柑9Kumquat
41	Loquat6李子
42	桑葚4Mulberry
43	Starfruit4杨桃
44	Cranberry5蓝莓
45	Jackfruit6珍珠果
46	无花果Fig9
47	Bilberry7蓝莓
48	黄杨果Yellow7PassionFruit
49	MonkFruit6石斛
50	枇杷7Loquat
…	…
98	AvocadoPear4鳄梨梨
99	桑葚Elderberry9

在这里插入图片描述

解决办法：

--创建表
CREATE TABLE fruit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL
);
--插入数据
INSERT INTO fruit (name) VALUES
('苹果Apple7'),
('Plum6李子'),
('Pineapple8菠萝'),
('Mango5芒果'),
('龙吐珠5Buddha''sHand'),
('Olive9橄榄'),
('Raspberry4树莓'),
('Apricot5杏子'),
('Grapefruit9柚子'),
('火龙果Dragonfruit9'),
('倒挂金钟Hanging6LobsterClaw'),
('巨峰葡萄Concord5Grape'),
('Lingonberry5红莓'),
('甜橙7SweetOrange'),
('金桔8Kumquat'),
('金橘5Tangerine'),
('玫瑰果6RoseHip'),
('硬木仁Brazil7Nut'),
('Pomegranate8石榴'),
('黑莓Blackberry5'),
('Banana5香蕉'),
('橙子Orange7'),
('Grape7葡萄'),
('草莓4Strawberry'),
('Watermelon6西瓜'),
('梨9Pear'),
('Peach9桃子'),
('樱桃Cherry6'),
('Lemon9柠檬'),
('木瓜Papaya9'),
('杨梅Bayberry7'),
('Pomelo4柚子'),
('榴莲Durian8'),
('荔枝Lychee8'),
('Blueberry5蓝莓'),
('樱桃番茄Cherry9Tomato'),
('Rambutan6红毛丹'),
('Avocado5牛油果'),
('椰子5Coconut'),
('柚柑9Kumquat'),
('Loquat6李子'),
('桑葚4Mulberry'),
('Starfruit4杨桃'),
('Cranberry5蓝莓'),
('Jackfruit6珍珠果'),
('无花果Fig9'),
('Bilberry7蓝莓'),
('黄杨果Yellow7PassionFruit'),
('MonkFruit6石斛'),
('枇杷7Loquat'),
('蜜瓜7Cantaloupe'),
('鳄梨（牛油果）4AlligatorPear'),
('龙眼8Longan'),
('Blackcurrant6黑加仑'),
('火龙果Pitaya4'),
('NashiPear5橙梨'),
('Pomelo8柚'),
('覆盆子7Boysenberry'),
('Juniper9Berry杜松子酒浆果'),
('Tangerine4沙糖桔'),
('硕果Jujube7'),
('Juniper杜松子5'),
('黑莓Boysenberry5'),
('雪梨SnowPear8'),
('亚洲梨AsianPear9'),
('鸽梨9PigeonPlum'),
('蓝莓9Huckleberry'),
('黑桃9BlackPlum'),
('李子4Greengage'),
('Wild5Grape野葡萄'),
('Raspberry6覆盆子'),
('HamiMelon8哈密瓜'),
('黄金果Golden6Kiwi'),
('硕果仁Sapote5'),
('青梅Green7Plum'),
('红丝绒香蕉9RedVelvetBanana'),
('樱李子4RainierCherry'),
('鸭梨9DuckPear'),
('龙舌兰9Agave'),
('木瓜9Pawpaw'),
('红提子4RedCurrant'),
('桃仁Peach6PalmFruit'),
('杨桃Carambola5'),
('Yumberry7杨梅'),
('JujubeDate9青枣'),
('MorelloCherry6樱桃李'),
('ChineseLanternFruit灯笼果9'),
('紫葡萄ConcordGrape9'),
('Gage李子4'),
('黑枸杞BlackGojiBerry5'),
('WingedBean翅果8'),
('硕果科Sapodilla8'),
('樱桃杏NankingCherry8'),
('灯笼果GroundCherry4'),
('ChineseBayberry6荔子'),
('BlueHoneysuckle5蓝莓挺柿子'),
('GojiBerry5枸杞'),
('AvocadoPear4鳄梨梨'),
('桑葚Elderberry9');

解决SQL：

SELECT
  REGEXP_SUBSTR(name, '[\\p{Han}]+') AS 品名,
  REGEXP_SUBSTR(name, '[A-Za-z]+') AS 英文名,
  REGEXP_SUBSTR(name, '[0-9]+(?=$|[^0-9])') AS 价格
FROM
  fruit;



第二种写法：

SELECT
    TRIM(REGEXP_REPLACE(name, '[^\\x{4e00}-\\x{9fa5}]', '')) AS 品名,
    TRIM(REGEXP_REPLACE(name, '[^a-zA-Z\' ]', '')) AS 英文名,
    TRIM(REGEXP_REPLACE(name, '[^0-9]', '')) AS 价格
FROM
    fruit;
	
注释：

这段查询使用了 REGEXP_REPLACE 函数来分别提取中文字符、英文字符和数字字符：

TRIM(REGEXP_REPLACE(name, '[^\\x{4e00}-\\x{9fa5}]', '')) 提取中文字符，并使用 TRIM 去除首尾的空格。
[^\\x{4e00}-\\x{9fa5}] 表示非中文字符范围的字符，将它们替换为空字符串。
TRIM(REGEXP_REPLACE(name, '[^a-zA-Z\' ]', '')) 提取英文字符，并使用 TRIM 去除首尾的空格。
[^a-zA-Z\' ] 表示非英文字符的字符，将它们替换为空字符串。包含空格和撇号以保留英文单词中的空格和撇号。
TRIM(REGEXP_REPLACE(name, '[^0-9]', '')) 提取数字字符，并使用 TRIM 去除首尾的空格。
[^0-9] 表示非数字字符的字符，将它们替换为空字符串。

最终效果：

品名	英文名	价格
苹果	Apple	7
李子	Plum	6
菠萝	Pineapple	8
芒果	Mango	5
龙吐珠	Buddha’s Hand	5
橄榄	Olive	9
树莓	Raspberry	4
杏子	Apricot	5
柚子	Grapefruit	9
火龙果	Dragonfruit	9
倒挂金钟	Hanging Lobster Claw	6
巨峰葡萄	Concord Grape	5
红莓	Lingonberry	5
甜橙	Sweet Orange	7
金桔	Kumquat	8
金橘	Tangerine	5
玫瑰果	Rose Hip	6
硬木仁	Brazil Nut	7
石榴	Pomegranate	8
黑莓	Blackberry	5
香蕉	Banana	5
橙子	Orange	7
葡萄	Grape	7
草莓	Strawberry	4
西瓜	Watermelon	6
梨	Pear	9
桃子	Peach	9
樱桃	Cherry	6
柠檬	Lemon	9
木瓜	Papaya	9
杨梅	Bayberry	7
柚子	Pomelo	4
榴莲	Durian	8
荔枝	Lychee	8
蓝莓	Blueberry	5
樱桃番茄	Cherry Tomato	9
红毛丹	Rambutan	6
牛油果	Avocado	5
椰子	Coconut	5
柚柑	Kumquat	9
李子	Loquat	6
桑葚	Mulberry	4
杨桃	Starfruit	4
蓝莓	Cranberry	5
珍珠果	Jackfruit	6
无花果	Fig	9
蓝莓	Bilberry	7
黄杨果	Yellow Passion Fruit	7
石斛	Monk Fruit	6
枇杷	Loquat	7
蜜瓜	Cantaloupe	7
鳄梨（牛油果）	Alligator Pear	4
龙眼	Longan	8
黑加仑	Blackcurrant	6
火龙果	Pitaya	4
橙梨	Nashi Pear	5
柚	Pomelo	8
覆盆子	Boysenberry	7
杜松子酒浆果	Juniper Berry	9
沙糖桔	Tangerine	4
硕果	Jujube	7
杜松子	Juniper	5
黑莓	Boysenberry	5
雪梨	Snow Pear	8
亚洲梨	Asian Pear	9
鸽梨	Pigeon Plum	9
蓝莓	Huckleberry	9
黑桃	Black Plum	9
李子	Greengage	4
野葡萄	Wild Grape	5
覆盆子	Raspberry	6
哈密瓜	Hami Melon	8
黄金果	Golden Kiwi	6
硕果仁	Sapote	5
青梅	Green Plum	7
红丝绒香蕉	Red Velvet Banana	9
樱李子	Rainier Cherry	4
鸭梨	Duck Pear	9
龙舌兰	Agave	9
木瓜	Pawpaw	9
红提子	Red Currant	4
桃仁	Peach Palm Fruit	6
杨桃	Carambola	5
杨梅	Yumberry	7
青枣	Jujube Date	9
樱桃李	Morello Cherry	6
灯笼果	Chinese Lantern Fruit	9
紫葡萄	Concord Grape	9
李子	Gage	4
黑枸杞	Black Goji Berry	5
翅果	Winged Bean	8
硕果科	Sapodilla	8
樱桃杏	Nanking Cherry	8
灯笼果	Ground Cherry	4
荔子	Chinese Bayberry	6
蓝莓挺柿子	Blue Honeysuckle	5
枸杞	Goji Berry	5
鳄梨梨	Avocado Pear	4
桑葚	Elderberry	9

注释：

SELECT
  -- 使用 REGEXP_SUBSTR 函数提取 name 字段中所有连续的汉字作为品名
  REGEXP_SUBSTR(name, '[\\p{Han}]+') AS 品名,
  
  -- 使用 REGEXP_SUBSTR 函数提取 name 字段中所有连续的英文字母作为英文名
  -- 这里假设英文名没有数字，并且英文名是连续的
  REGEXP_SUBSTR(name, '[A-Za-z]+') AS 英文名,
  
  -- 使用 REGEXP_SUBSTR 函数提取 name 字段中所有的数字作为价格
  -- 正则表达式 '[0-9]+' 匹配一个或多个数字
  -- 正向前瞻断言 (?=$|[^0-9]) 确保数字后面是字符串的结尾或者不是一个数字
  -- 这样确保价格数字后面没有紧跟其他数字
  REGEXP_SUBSTR(name, '[0-9]+(?=$|[^0-9])') AS 价格
FROM
  -- 指定从 fruit 表中选择记录
  fruit;