Oracle substr函数用法

LNWOF • 2025-01-24 18:46 • 编程

一、基础用法

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操作的时候需要处理内存溢出等问题。

原创文章，作者：LNWOF，如若转载，请注明出处：https://www.506064.com/n/332410.html