oracle数据库字符串截取字符串,[基础教程]Oracle数据库字符串截取实战

SUBSTR函数介绍

substr函数在oracle数据库中的功能为：字符串截取函数，它可以截取指定位置段的字符串信息

substr函数语法:

substr(string str, int a, [int b]);

参数说明:

str:待截取的字符串

a:字符串截取位置，当此值为0或1时，代表截取字符串从第一位开始截取，如果a是负数，则从string字符串末尾开始算起。

b:可选项，表示待截取字符串的长度，当不输入此参数时，则截取后面的全部字符串

示例：

--从第0个字符开始，截取所有字符串，返回'Hello www.ronpris.com'

SELECT SUBSTR('Hello www.ronpris.com', 0) FROM dual;

--从第1个字符开始，截取所有字符串，返回'Hello www.ronpris.com',

SELECT SUBSTR('Hello www.ronpris.com', 1) FROM dual

--从第7个字符开始，截取到末尾。返回'www.ronpris.com'

SELECT SUBSTR('Hello www.ronpris.com', 7) FROM dual;

--从倒数第11个字符开始，截取到末尾。返回'ronpris.com'

SELECT SUBSTR('Hello www.ronpris.com', -11) FROM dual;

--从第7个字符开始，截取15个字符。返回'www.ronpris.com'

SELECT SUBSTR('Hello www.ronpris.com', 7, 15) FROM dual;

--从倒数第11个字符开始，截取7个字符。返回'ronpris'

SELECT SUBSTR('Hello www.ronpris.com', -11, 7) FROM dual;

INSTR函数介绍

instr函数是对某个字符串进行判断，判断其是否含有指定的字符，在一个字符串中查找指定的字符，

返回被查找到的指定的字符的位置。

instr函数语法：

instr(sourceString,childString,[start],[showTime])

instr('源字符串' , '目标字符串' ,'开始位置','第几次出现')

参数说明：

sourceString代表源字符串；

childString代表要从源字符串中查找的子串；

start代表查找的开始位置，这个参数可选的，默认为1；

showTime代表想从源字符中查找出第几次出现的childString，这个参数也是可选的， 默认为1

如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。

返回值为：查找到的字符串的位置。如果没有查找到，返回0。

示例：

--表示从源字符串'http://www.ronpris.com'中第1个字符开始查找子字符串'.'第1次出现的位置，结果返回12

SELECT INSTR('http://www.ronpris.com', '.') FROM dual;

--表示从源字符串'http://www.ronpris.com'中第5个字符开始查找子字符串'.'第1次出现的位置,结果返回20，

--第一次出现时12位置，第二次出现时20位置，因为是从13位置开始查找，所以第一次出现的位置是是20

SELECT INSTR('http://www.ronpris.com', '.', 13) FROM dual;

--表示从源字符串'http://www.ronpris.com'中第5个字符开始查找子字符串'.'第1次出现的位置,结果返回20

SELECT INSTR('http://www.ronpris.com', '.', 5, 1) FROM dual;

--表示从源字符串'http://www.ronpris.com'中第3个字符开始查找子字符串'.'第2次出现的位置,结果返回20

SELECT INSTR('http://www.ronpris.com', '.', 3, 2) FROM dual;

--start参数为-7，从右向左检索，查找'.'字符串在源字符串中第1次出现的位置，结果返回20

SELECT INSTR('http://www.ronpris.com', '.', -1, 1) FROM dual;

--start参数为-1，从右向左检索，查找'.'字符串在源字符串中第2次出现的位置，结果返回12

SELECT INSTR('http://www.ronpris.com', '.', -1, 2) FROM dual;

实战SUBSTR函数和INSTR函数混合使用

实战需求：数据查询处理需要对sourceString进行"拆分"，获取不同的数据内容。

sourceString命名规则类似：员工姓名_员工职位_员工部门_员工地址

其中，员工姓名、职位、部门、地址等字符串长度不固定，由于字符串长度不固定，只使用substr函数无法实现需求，需配合instr函数定位到字符'_'的位置，然后使用substr函数进行截取。详细见下面例子。

表数据如下：

员工姓名_员工职位_员工部门_员工地址

SELECT * FROM ODS.TABLE_CODE;

获取员工姓名：

SELECT SUBSTR (SOURCE_CODE,1,  INSTR (SOURCE_CODE, '_', 1, 1) - 1) AS ENAME from ODS.TABLE_CODE;

返回结果：

SQL分析：此处截取源字符串SOURCE_CODE，从第1个字符开始，由于代表员工姓名的长度不固定，我们无法确定截取几个字符，所以需要使用instr函数判断第一个'_'字符的位置，进而确定每个SOURCE_CODE截取几个字符串，

而INSTR (SOURCE_CODE, '_', 1, 1)代表获取的字符'_'的位置，而员工姓名的位置是在'_'之前，所以需要进行-1处理，从而获取员工姓名字符串。

获取员工职位：

SELECT SUBSTR ( SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 1) + 1,  INSTR (SOURCE_CODE, '_', 1, 2) - INSTR (SOURCE_CODE, '_', 1, 1)-1 ) AS JOB FROM ODS.TABLE_CODE;

返回结果：

SQL分析：截取源字符串，从(第一个'_'出现位置+1)开始，截取个数为：第2个'_'出现位置减去第1个'_'出现位置，此时还多了一个下划线'_'，再减去1即可得到代表员工职位字符串。

获取员工所在部门：

SELECT SUBSTR ( SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 2) + 1,  INSTR (SOURCE_CODE, '_', 1, 3) - INSTR (SOURCE_CODE, '_', 1, 2) -1 ) AS DEPT_NAME FROM ODS.TABLE_CODE;

返回结果：

SQL分析：截取源字符串，从(第二个'_'出现位置+1)开始，截取个数为：第三个'_'出现位置减去第二个'_'出现位置，此时还多了一个下划线'_'，再减去1即可得到代表员工职位字符串。

获取员工地址：

SELECT SUBSTR (SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 3) + 1) AS ADDRESS FROM ODS.TABLE_CODE;

返回结果：

SQL分析：截取源字符串，从(字符串'_'第3次出现位置+1)开始截取，+1代表字符串'_'是在所需要的字符串之前，需要+1,然后截取到末尾，即可获得员工地址。

Oracle截取总结

以上所述是小生给大家介绍的Oracle中字符串截取常用方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小生会及时回复大家的。