MySQL - REGEXP_SUBSTR() 函數

你好,未來的數據庫魔法師們!今天,我們將踏上一段令人興奮的旅程,探索 MySQL 的 REGEXP_SUBSTR() 函數。如果你是編程新手,別擔心——我將成為你的友好導遊,一步步為你解釋一切。所以,來一杯咖啡,我們一起來深入探討吧!

MySQL - regexp_substr() Function

REGEXP_SUBSTR() 是什麼?

在我們開始編寫代碼之前,讓我們先了解 REGEXP_SUBSTR() 的相關知識。想像你是一名偵探,試圖在一長串文字中尋找特定的模式。這正是 REGEXP_SUBSTR() 所做的事——它就像是你數據庫中的超級智能放大鏡!

REGEXP_SUBSTR() 是 MySQL中的一個函數,它使用正則表達式來搜索和從字符串中提取特定的模式。它就像是你數據庫工具箱中的瑞士軍刀一樣,用於文本操作。

語法

這是 REGEXP_SUBSTR() 函數的樣式:

REGEXP_SUBSTR(subject, pattern[, position[, occurrence[, match_type]]])

別讓這個嚇到你!我們會一點一點地解析:

  • subject:我們要搜索的字符串(我們的干草堆)
  • pattern:我們要尋找的正則表達式模式(我們的針)
  • position:從哪裡開始搜索(可選,默認值為 1)
  • occurrence:如果有多個匹配,要返回哪個(可選,默認值為 1)
  • match_type:額外的匹配選項(可選)

基本使用

讓我們從一個簡單的例子開始,讓我們的腳濕一下:

SELECT REGEXP_SUBSTR('Hello, World!', 'World') AS result;

如果你運行這個查詢,你將得到:

result
World

這裡發生了什麼?我們要求 MySQL 在字符串 'Hello, World!' 中找到單詞 'World',它做到了!

讓我們更有創意

現在我們試試更具挑戰性的事情。假設我們有一個電子郵件地址列表,我們想要提取只是域名:

SELECT REGEXP_SUBSTR('[email protected]', '@(.+)$') AS domain;

結果:

domain
@example.com

哦哦!我們也得到 '@' 符號。讓我們精煉一下我們的模式:

SELECT REGEXP_SUBSTR('[email protected]', '@(.+)$', 1, 1, 'c') AS domain;

結果:

domain
example.com

好多了!match_type 參數中的 'c' 告訴 MySQL 返回捕獲的組(括號中的部分),而不是整個匹配。

使用真實數據

讓我們創建一個小表來練習:

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100)
);

INSERT INTO users (email) VALUES
('[email protected]'),
('[email protected]'),
('[email protected]');

現在我們提取所有的域名:

SELECT email, REGEXP_SUBSTR(email, '@(.+)$', 1, 1, 'c') AS domain
FROM users;

結果:

email domain
[email protected] gmail.com
[email protected] yahoo.com
[email protected] hotmail.com

這不是很棒嗎?我們只用一個查詢就提取了所有的域名!

高級技巧

多個出現次數

有時候,你可能想要找到模式的多次出現。讓我們看看怎麼做:

SELECT REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 1) AS first_fruit,
REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 2) AS second_fruit,
REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 3) AS third_fruit;

結果:

first_fruit second_fruit third_fruit
apple banana cherry

這裡,我們使用 [^,]+ 來匹配任何不是逗號的字符。數字在末尾(1, 2, 3)告訴 MySQL 返回哪個出現次數。

不區分大小寫匹配

如果我們想要不區分大小寫來匹配模式,我們可以使用 'i' 選項:

SELECT REGEXP_SUBSTR('The Quick Brown Fox', 'quick', 1, 1, 'i') AS result;

結果:

result
Quick

即使我們搜索的是小寫的 'quick',我們也能找到字符串中的 'Quick'!

在客戶端程序中使用 REGEXP_SUBSTR() 函數

雖然 MySQL Workbench 非常適合運行查詢,但你也可以在多種編程語言中使用 REGEXP_SUBSTR()。這裡有一個 Python 的快速示例:

import mysql.connector

# 連接到你的 MySQL 數據庫
cnx = mysql.connector.connect(user='your_username', password='your_password',
host='127.0.0.1', database='your_database')
cursor = cnx.cursor()

# 執行一個使用 REGEXP_SUBSTR() 的查詢
query = "SELECT REGEXP_SUBSTR('Hello, World!', 'World') AS result"
cursor.execute(query)

# 獲取並打印結果
result = cursor.fetchone()
print(f"結果是: {result[0]}")

# 記得關閉連接
cnx.close()

這個腚本連接到你的 MySQL 數據庫,運行一個簡單的 REGEXP_SUBSTR() 查詢,並打印結果。

結論

恭喜你!你剛剛踏入了強大的 REGEXP_SUBSTR() 世界。記住,像任何好的偵探工具一樣,它需要練習才能精通。不要害怕嘗試不同的模式和選項。

這裡有一個方便的表格,總結了 REGEXP_SUBSTR() 的參數:

參數 描述 可選?
subject 要搜索的字符串
pattern 要搜索的正則表達式模式
position 從哪裡開始搜索
occurrence 返回哪個發現的匹配
match_type 預設的匹配選項(例如,'c', 'i')

繼續練習,很快你就能像專家一樣從字符串中提取數據!記住,在數據庫世界中,能夠操作和提取特定數據是一種超能力。而現在,你正在成為一名超級英雄的途中!

Credits: Image by storyset