2025年09月19日玄貓（BlackCat）

資料函式庫字串與日期時間處理函式詳解

本文探討資料函式庫中常用的字串與日期時間處理函式，涵蓋計算長度、修改大小寫、去除字元、連線字串、搜尋子字串、擷取子字串、字串替換以及正規表示式的應用。同時也詳細說明瞭如何在不同資料函式庫系統（如 MySQL、Oracle、PostgreSQL、SQL Server 和

資料函式庫 SQL

SQL 字串函式日期時間函式正規表示式資料函式倉管理系統資料函式庫查詢

資料函式庫的字串處理和日期時間操作是資料處理的根本，不同資料函式庫系統提供的函式略有差異，但核心功能相似。字串處理包含長度計算、大小寫轉換、去除多餘字元、字串連線、子字串搜尋和擷取等操作，熟練運用這些函式可以有效地整理和分析資料。日期時間函式則允許我們取得當前時間、進行日期加減運算，以及計算日期時間差異，對於時間序列資料的分析至關重要。此外，正規表示式為更進階的字串處理提供了強大的模式匹配能力，活用正規表示式可以大幅提升資料處理的效率和精準度，不同資料函式庫系統的正規表示式支援程度不一，需要根據實際情況選擇合適的函式和語法。

字串處理函式與操作詳解

在資料函式倉管理系統中，字串處理是一項基本且重要的功能。不同的資料函式庫系統（如 MySQL、Oracle、PostgreSQL、SQL Server 和 SQLite）提供了多種函式來處理字串，包括計算長度、修改大小寫、去除不需要的字元、連線字串、搜尋子字串以及擷取部分字串等。

計算字串長度

大多數關聯式資料函式倉管理系統（RDBMS）在計算字串長度時，會忽略尾部的空格，但 Oracle 會將這些空格計算在內。

示例：

假設有一個字串 'Al '，在大多數 RDBMS 中其長度為 2，而在 Oracle 中則為 5。

若要在 Oracle 中去除尾部空格，可以使用 TRIM 函式：

SELECT LENGTH(TRIM(name)) FROM my_table;

修改字串大小寫

可以使用 UPPER 和 LOWER 函式來轉換字串的大小寫。

示例：

-- 將 type 欄位轉換為大寫
SELECT UPPER(type) FROM my_table;

-- 將 type 欄位轉換為小寫並進行比較
SELECT * FROM my_table WHERE LOWER(type) = 'public';

Oracle 和 PostgreSQL 還提供了 INITCAP 函式，用於將每個單詞的首字母轉換為大寫，其餘字母轉換為小寫。

去除不需要的字元

使用 TRIM 函式可以去除字串前後的特定字元。

示例：

假設 my_table 表中有以下資料：

+
---
-
---
---
+
| color    |
+
---
-
---
---
+
| !!red    |
| .orange! |
| ..yellow..|
+
---
-
---
---
+

預設情況下，TRIM 去除前後空格，但也可以指定去除其他字元：

-- 去除前後空格
SELECT TRIM(color) AS color_clean FROM my_table;

-- 去除前後 '!' 字元
SELECT TRIM('!' FROM color) AS color_clean FROM my_table;

在 SQLite 中，可以使用 TRIM(color, '!') 來達到同樣效果。

字串連線

可以使用 CONCAT 函式或 || 運算元來連線字串。

示例：

-- MySQL, PostgreSQL 和 SQL Server 使用 CONCAT
SELECT CONCAT(id, '_', name) AS id_name FROM my_table;

-- Oracle, PostgreSQL 和 SQLite 使用 ||
SELECT id || '_' || name AS id_name FROM my_table;

搜尋子字串

有兩種方法可以搜尋子字串：使用 LIKE 運算子檢查是否包含特定文字，或使用 INSTR、POSITION、CHARINDEX 等函式查詢子字串的位置。

示例：

-- 使用 LIKE 搜尋包含 'some' 的行
SELECT * FROM my_table WHERE my_text LIKE '%some%';

-- 使用 INSTR 查詢 'some' 的位置
SELECT INSTR(my_text, 'some') AS some_location FROM my_table;

不同 RDBMS 使用不同的函式來查詢子字串的位置，如下表所示：

| RDBMS | 函式格式 | |

-|

擷取部分字串

使用 SUBSTR 或 SUBSTRING 函式可以擷取字串的一部分。

示例：

-- 使用 SUBSTR 擷取子字串
SELECT SUBSTR(my_text, start, length) FROM my_table;

-- 使用 SUBSTRING（MySQL、PostgreSQL 和 SQL Server）
SELECT SUBSTRING(my_text, start, length) FROM my_table;

不同 RDBMS 對這些函式的引數有不同的要求。

此圖示展示了不同字串操作的流程選擇。開發者可以根據具體需求選擇合適的操作來處理字串資料。

綜上所述，熟練掌握這些字串處理函式和操作，可以顯著提升資料函式庫操作的效率和靈活性。不同 RDBMS 之間的語法差異需要特別注意，以確保 SQL 陳述式的正確性和相容性。

字串處理函式在資料函式庫中的應用

在資料函式倉管理系統中，字串處理是一項常見且重要的任務。無論是提取特定資訊、替換文字內容，還是刪除不需要的字元，都需要藉助字串函式來實作。本文將探討幾種常用的字串處理函式及其在不同資料函式庫系統中的應用。

提取子字串

提取子字串是資料函式庫操作中常見的需求。不同的資料函式庫系統提供了不同的函式來實作這一功能。例如，在 SQL Server 中，可以使用 SUBSTRING 函式，而在 Oracle 和 PostgreSQL 中，則可以使用 SUBSTR 函式。

SUBSTR 函式的使用

SUBSTR 函式的基本語法如下：

SUBSTR(string, start, length)

其中，string 是要操作的原始字串，start 是開始提取的位置（從 1 開始計數），length 是要提取的字元數量。

考慮以下範例表格：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| my_text                      |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| Here is some text.           |
| And some numbers - 1 2 3 4 5 |
| And some punctuation! :)     |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+

要提取子字串，可以使用以下 SQL 陳述式：

SELECT SUBSTR(my_text, 14, 8) AS sub_str
FROM my_table;

結果如下：

+
---
-
---
---
+
| sub_str  |
+
---
-
---
---
+
| text.    |
| ers - 1  |
| tuation! |
+
---
-
---
---
+

#### 內容解密：

SUBSTR(my_text, 14, 8) 表示從 my_text 欄位的第 14 個字元開始，提取 8 個字元。
start 引數若為負數，則從字串末尾開始計數。
若省略 length，則提取從 start 位置到字串末尾的所有字元。

字串替換

字串替換是另一項常見的操作。可以使用 REPLACE 函式來實作。

REPLACE 函式的使用

REPLACE 函式的基本語法如下：

REPLACE(string, old_string, new_string)

其中，string 是原始字串，old_string 是要被替換的子字串，new_string 是替換後的新子字串。

考慮以下範例：

SELECT REPLACE(my_text, 'some', 'the') AS new_text
FROM my_table;

結果如下：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| new_text                    |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| Here is the text.           |
| And the numbers - 1 2 3 4 5 |
| And the punctuation! :)     |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
--+

#### 內容解密：

REPLACE(my_text, 'some', 'the') 將 my_text 中的 'some' 替換為 'the'。
若將 new_string 設定為空字串，則可實作刪除特定子字串的功能。

使用正規表示式

正規表示式提供了強大的模式匹配能力，可以用於複雜的字串處理任務。

正規表示式範例

假設有以下食譜資料：

- 1 tablespoon chili powder
- .5 tablespoon ground cumin
- .5 teaspoon paprika
- .25 teaspoon garlic powder
- .25 teaspoon onion powder
- .25 teaspoon crushed red pepper flakes
- .25 teaspoon dried oregano

要提取成分名稱（即「spoon」之後的文字），可以使用以下正規表示式：

(?<=spoon ).*$

結果如下：

chili powder
ground cumin
paprika
garlic powder
onion powder
crushed red pepper flakes
dried oregano

#### 內容解密：

(?<=spoon ).*$ 表示匹配任何位於「spoon 」之後直到行尾的文字。
正規表示式的語法可能較為複雜，建議使用線上工具（如 Regex101）進行除錯和學習。

不同資料函式庫系統中的正規表示式支援

不同的資料函式庫系統對正規表示式的支援程度不同。例如，MySQL 使用 REGEXP 運算子進行正規表示式匹配，而 Oracle 和 PostgreSQL 則提供了專門的正規表示式函式，如 REGEXP_SUBSTR 和 REGEXP_REPLACE。

MySQL 中的正規表示式

在 MySQL 中，可以使用 REGEXP 運算子進行模式匹配。例如：

SELECT *
FROM movies
WHERE city REGEXP '(Chicago|CHI|Chitown)';

結果如下：

+
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
--+
| title                    | city    |
+
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
--+
| The Blues Brothers       | Chicago |
| Ferris Bueller's Day Off | Chi     |
+
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
--+

#### 內容解密：

(Chicago|CHI|Chitown) 表示匹配「Chicago」、「CHI」或「Chitown」。
MySQL 的正規表示式預設不區分大小寫。

資料函式庫中的正規表示式與字串函式應用

在資料函式庫查詢中，正規表示式是一種強大的工具，能夠幫助我們在字串中進行複雜的模式匹配和處理。不同的資料函式倉管理系統（DBMS）對正規表示式的支援程度不同，但大多數主流的DBMS都提供了相關的功能。

MySQL中的正規表示式

MySQL支援正規表示式，但需要注意的是，在MySQL的正規表示式中，反斜線（\）需要被轉義為雙反斜線（\）。例如，若要匹配數字，需要使用\\d。

Oracle中的正規表示式

Oracle提供了多種正規表示式函式，包括REGEXP_LIKE、REGEXP_COUNT、REGEXP_INSTR、REGEXP_SUBSTR和REGEXP_REPLACE等。這些函式能夠實作複雜的字串匹配和處理。

查詢範例：找出標題中包含數字的電影

SELECT *
FROM movies
WHERE REGEXP_LIKE(title, '\d');

上述查詢使用了REGEXP_LIKE函式來找出movies表中，標題（title）包含數字的記錄。

計算標題中大寫字母的數量

SELECT title, REGEXP_COUNT(title, '[A-Z]') AS num_caps
FROM movies;

這個查詢使用REGEXP_COUNT函式來計算每個電影標題中大寫字母的數量。

替換標題中的數字為100

SELECT REGEXP_REPLACE(title, '[0-9]+', '100') AS one_hundred_title
FROM movies;

這裡使用了REGEXP_REPLACE函式，將標題中的數字替換為100。

PostgreSQL中的正規表示式

PostgreSQL支援使用SIMILAR TO和~運算元來進行正規表示式的匹配。其中，~運算元支援更複雜的正規表示式。

查詢範例：找出城市名稱為Chicago或其變體的記錄

SELECT *
FROM movies
WHERE city SIMILAR TO '(Chicago|CHI|Chi|Chitown)';

使用`~`運算元找出標題中包含數字的電影

SELECT *
FROM movies
WHERE title ~ '\d';

PostgreSQL也支援REGEXP_REPLACE函式，用於替換字串中符合特定模式的子串。

SQL Server中的正規表示式

SQL Server對正規表示式的支援相對有限，主要透過LIKE關鍵字實作簡單的模式匹配。

查詢範例：找出標題中包含數字的電影

SELECT *
FROM movies
WHERE title LIKE '%[0-9]%';

資料型別轉換

在對非字串型別的資料使用字串函式時，可能需要進行資料型別的轉換。例如，在PostgreSQL中，需要使用CAST函式將數值型別轉換為字串型別，才能對其使用字串函式，如LENGTH。

SELECT LENGTH(CAST(numbers AS CHAR(5))) AS len_num
FROM my_table;

這種轉換是暫時的，不會改變表中資料的原始型別。如果需要永久改變資料型別，可以透過修改表的結構來實作。

SQL中的日期和時間函式

在SQL中，日期和時間函式對於處理包含日期和時間資料的欄位至關重要。本篇文章將介紹多種廣泛用於SQL的日期和時間函式。

取得當前日期或時間

不同的資料函式庫系統提供了多種函式來取得當前的日期、時間或日期時間。以下是一些範例：

MySQL、PostgreSQL和SQLite：

SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;

Oracle：

SELECT CURRENT_DATE FROM dual;
SELECT CAST(CURRENT_TIMESTAMP AS TIME) FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;

SQL Server：

SELECT CAST(CURRENT_TIMESTAMP AS DATE);
SELECT CAST(CURRENT_TIMESTAMP AS TIME);
SELECT CURRENT_TIMESTAMP;

除了上述函式外，不同的資料函式庫系統還有其他等效的函式，例如MySQL中的CURDATE()和SQL Server中的GETDATE()。

日期和時間的加減運算

日期和時間的值可以進行加減運算，例如增加或減少年、月、日、時、分、秒等。下面是一些範例：

日期減一天

MySQL：

SELECT CURRENT_DATE - INTERVAL 1 DAY;
SELECT SUBDATE(CURRENT_DATE, 1);
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);

Oracle：

SELECT CURRENT_DATE - INTERVAL '1' DAY FROM dual;

PostgreSQL：

SELECT CAST(CURRENT_DATE - INTERVAL '1 day' AS DATE);

SQL Server：

SELECT CAST(CURRENT_TIMESTAMP - 1 AS DATE);
SELECT DATEADD(DAY, -1, CAST(CURRENT_TIMESTAMP AS DATE));

SQLite：
```
SELECT DATE(CURRENT_DATE, '-1 day');
```

日期時間加三小時

MySQL：

SELECT CURRENT_TIMESTAMP + INTERVAL 3 HOUR;
SELECT ADDDATE(CURRENT_TIMESTAMP, INTERVAL 3 HOUR);
SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 3 HOUR);

Oracle：

SELECT CURRENT_TIMESTAMP + INTERVAL '3' HOUR FROM dual;

PostgreSQL：

SELECT CURRENT_TIMESTAMP + INTERVAL '3 hours';

SQL Server：

SELECT DATEADD(HOUR, 3, CURRENT_TIMESTAMP);

SQLite：

SELECT DATETIME(CURRENT_TIMESTAMP, '+3 hours');

日期和時間的差異計算

可以計算兩個日期、時間或日期時間之間的差異，單位可以是年、月、日、時、分、秒等。

日期差異（天數）

假設有一張表格，包含起始日期和結束日期欄位。

+
---
-
---
-
---
-+
---
-
---
-
---
-+
| start_date | end_date   |
+
---
-
---
-
---
-+
---
-
---
-
---
-+
| 2016-10-10 | 2020-11-11 |
| 2019-03-03 | 2021-04-04 |
+
---
-
---
-
---
-+
---
-
---
-
---
-+

計算兩個日期之間的天數差異的SQL陳述式如下：

MySQL：

SELECT DATEDIFF(end_date, start_date) AS day_diff FROM my_table;

Oracle：

SELECT (end_date - start_date) AS day_diff FROM my_table;

PostgreSQL：

SELECT AGE(end_date, start_date) AS day_diff FROM my_table;

SQL Server：

SELECT DATEDIFF(day, start_date, end_date) AS day_diff FROM my_table;

SQLite：

SELECT (julianday(end_date) - julianday(start_date)) AS day_diff FROM my_table;

執行結果如下：

+
---
-
---
---
+
| day_diff |
+
---
-
---
---
+
| 1493     |
| 763      |
+
---
-
---
---
+

時間差異（秒數）

假設有一張表格，包含起始時間和結束時間欄位。

+
---
-
---
-
---
-+
---
-
---
---
+
| start_time | end_time |
+
---
-
---
-
---
-+
---
-
---
---
+
| 10:30:00   | 11:30:00 |
| 14:50:32   | 15:22:45 |
+
---
-
---
-
---
-+
---
-
---
---
+

計算兩個時間之間的秒數差異的SQL陳述式如下：

MySQL：

SELECT TIMEDIFF(end_time, start_time) AS time_diff FROM my_table;

PostgreSQL：

SELECT EXTRACT(epoch from end_time - start_time) AS time_diff FROM my_table;

SQL Server：

SELECT DATEDIFF(second, start_time, end_time) AS time_diff FROM my_table;

SQLite：

SELECT (strftime('%s', end_time) - strftime('%s', start_time)) AS time_diff FROM my_table;

執行結果如下（MySQL傳回HH:MM:SS格式，而其他資料函式庫系統傳回秒數）：

-- MySQL
+
---
-
---
-
---
+
| time_diff |
+
---
-
---
-
---
+
| 01:00:00  |
| 00:32:13  |
+
---
-
---
-
---
+

-- PostgreSQL, SQL Server 和 SQLite
time_diff
---
-
---
-
---
3600
1933

日期時間差異（小時數）

假設有一張表格，包含起始日期時間和結束日期時間欄位。

+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
--+
| start_dt            | end_dt              |
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
--+
| 2016-10-10 10:30:00 | 2020-11-11 11:30:00 |
| 2019-03-03 14:50:32 | 2021-04-04 15:22:45 |
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
--+

計算兩個日期時間之間的時數差異的SQL陳述式如下：

MySQL：

SELECT TIMESTAMPDIFF(hour, start_dt, end_dt) AS hour_diff FROM my_table;

Oracle：

SELECT (end_dt - start_dt) AS hour_diff FROM my_table;

PostgreSQL：

SELECT AGE(end_dt, start_dt) AS hour_diff FROM my_table;

SQL Server：

SELECT DATEDIFF(hour, start_dt, end_dt) AS hour_diff FROM my_table;

SQLite：

SELECT ((julianday(end_dt) - julianday(start_dt)) * 24) AS hour_diff FROM my_table;

執行結果如下（不同資料函式庫系統的輸出格式可能不同）：

+
---
-
---
-
---
+
| hour_diff |
+
---
-
---
-
---
+
| 35833     |
| 18312     |
+
---
-
---
-
---
+

字串處理函式與操作詳解

計算字串長度

示例：

修改字串大小寫

示例：

去除不需要的字元

示例：

字串連線

示例：

搜尋子字串

示例：

| RDBMS | 函式格式 | |

-|

擷取部分字串

示例：

此圖示展示了不同字串操作的流程選擇。開發者可以根據具體需求選擇合適的操作來處理字串資料。

字串處理函式在資料函式庫中的應用

提取子字串

SUBSTR 函式的使用

#### 內容解密：

字串替換

REPLACE 函式的使用

#### 內容解密：

使用正規表示式

正規表示式範例

#### 內容解密：

不同資料函式庫系統中的正規表示式支援

MySQL 中的正規表示式

#### 內容解密：

資料函式庫中的正規表示式與字串函式應用

MySQL中的正規表示式

Oracle中的正規表示式

查詢範例：找出標題中包含數字的電影

計算標題中大寫字母的數量

替換標題中的數字為100

PostgreSQL中的正規表示式

查詢範例：找出城市名稱為Chicago或其變體的記錄

使用~運算元找出標題中包含數字的電影

SQL Server中的正規表示式

查詢範例：找出標題中包含數字的電影

資料型別轉換

SQL中的日期和時間函式

取得當前日期或時間

日期和時間的加減運算

日期減一天

日期時間加三小時

日期和時間的差異計算

日期差異（天數）

時間差異（秒數）

日期時間差異（小時數）

使用`~`運算元找出標題中包含數字的電影