Oracle substr函數用法

一、基礎用法

Oracle SQL中的substr函數是用來截取指定字符串的一部分，格式如下：

substr(string, start, length)

其中，string表示要截取的原字符串，start表示起始位置，length表示要截取的長度。舉個例子：

SELECT substr('Hello World', 7, 5) FROM dual;

這個語句的返回結果為「World」，因為從第7個字符（空格）開始，截取5位字符。

另外，如果start為負數，則表示從字符串的末尾開始向左計數；如果length為負數，則優先從右往左截取指定長度的字符。例如：

SELECT substr('Hello World', -5, 3) FROM dual;
SELECT substr('Hello World', 7, -2) FROM dual;

第一個語句返回結果為「Wor」，因為從倒數第5個字符向右截取了3位字符。第二個語句返回結果為「orl」，因為從第7個字符開始向右截取，但是由於length為-2，所以先向左截取2位字符。

二、在查詢中使用

substr函數在查詢語句中經常用來提取特定的字段信息，例如從一個日期字段中截取年份、月份或者日份。

假設有一個表格employee，其中有一個字段hire_date表示僱傭日期：

CREATE TABLE employee (
    empno NUMBER(4),
    ename VARCHAR2(10),
    hire_date DATE,
    salary NUMBER(7, 2)
);

要查詢所有員工的僱傭年份，可以使用以下語句：

SELECT ename, substr(hire_date, 1, 4) FROM employee;

其中，hire_date的格式默認為yyyy-mm-dd，所以substr的start為1，length為4就可以截取到年份部分。

三、字符串替換

substr函數也可以和replace函數一起使用，進行字符串替換。例如：

UPDATE employee SET ename = substr(ename, 1, 3) || 'AAA' WHERE empno = 1001;

這個語句會將empno為1001的員工的ename字段前三位保留，後面替換為「AAA」，例如「SMITH」會被替換為「SMIAAA」。

四、使用regexp_substr提取字段

在Oracle SQL中，如果要從一個字段中提取符合特定模式的子字符串，可以使用regexp_substr函數。例如，假設有如下表格：

CREATE TABLE mytable (
    id NUMBER(4),
    name VARCHAR2(20)
);
INSERT INTO mytable VALUES (1, 'Tom is 25 years old');
INSERT INTO mytable VALUES (2, 'Jerry is 30 years old');
INSERT INTO mytable VALUES (3, 'Lucy is 28 years old');

要從name字段中提取年齡部分，可以使用正則表達式進行匹配，然後結合substr函數提取。例如：

SELECT substr(regexp_substr(name, '[[:digit:]]+'), 1, 2)
FROM mytable;

這個語句的返回結果是：

25
30
28

其中，regexp_substr(name, ‘[[:digit:]]+’)表示從name字段中提取連續的數字部分，substr函數截取前兩位數字。

五、使用substr處理LOB字段

在Oracle數據庫中，LOB（large object）字段用於存儲大型數據，例如文本、圖片、音頻等。如果要對LOB字段進行截取或者替換，可以使用substr函數和DBMS_LOB包，具體方法如下：

DECLARE
    l_lob BLOB;
    l_offset NUMBER := 1;
    l_amount NUMBER := 10;
BEGIN
    SELECT image INTO l_lob FROM mytable WHERE id = 1 FOR UPDATE;

    -- 截取LOB字段的一部分
    DBMS_LOB.writeappend(l_lob, l_amount, substr('New data', 1, l_amount));

    -- 替換LOB字段的一部分
    DBMS_LOB.write(l_lob, l_amount, l_offset, substr('Other data', 1, l_amount));
END;

這個示例代碼演示了如何對BLOB字段進行截取和替換。首先使用SELECT語句查詢需要操作的LOB字段，然後使用DBMS_LOB.write或者DBMS_LOB.writeappend函數對數據庫進行寫操作，其中substr函數用於提取截取或者替換的部分。注意，由於LOB字段可能非常大，在進行DBMS_LOB操作的時候需要處理內存溢出等問題。