oracle利用substr和instr函数截取角色序列的第一个角色

假设角色表里parts有一个字段partlist，它的记录为：

partlist

147,225
136
147,225,224
148
131,149
129
223
132
224,147,225
14
1963
19,822,002
1964

现在想要的结果是截取角色字段里的第一个角色值，即如果有多个角色取第一个，有一个就去那个，如下所示：

partlist

147
136
147
148
131
129
223
132
224
14
1963
19
1964

那我们需要写的sql语句是：

select  substr(','||a.partlist||',',2,(instr(','||a.partlist||',',',',1,2)-2)) as partlist

from parts

里面用到两个函数substr和instr。

下面说明这两个函数的用法：

（1）INSTR的用法

INSTR方法的格式为
INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)

例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置。

默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。

所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是

Instring
——————
14

（2）substr的用法

In oracle/PLSQL, the substr functions allows you to extract a substring from a string.

The syntax for the substr function is:

substr( string, start_position, [ length ] )


说明：
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

For example:
     substr('This is a test', 6, 2)          would return 'is'
     substr('This is a test', 6)              would return 'is a test'
     substr('TechOnTheNet', 1, 4)     would return 'Tech'
     substr('TechOnTheNet', -3, 3)    would return 'Net'
     substr('TechOnTheNet', -6, 3)     would return 'The' 。

在本例中，首先是用','||a.partlist||','给parlist字段的每条记录的前面和后面都加上一个“,”号，然后利用instr函数instr(','||a.partlist||',',',',1,2)从第一数开始查找获取记录中第二个","出现的位置。

因为刚才在parlist前面和后面都加上了","，所以我们只要把刚才返回的值再减去2就可以得到角色列表中第一个角色的长度(instr(','||a.partlist||',',',',1,2)-2)。

接着利用substr函数就可以很容易的截取角色列表中的角色了。

 substr(','||a.partlist||',',2,(instr(','||a.partlist||',',',',1,2)-2))

','||a.partlist||','：给parlist字段的每条记录的前面和后面都加上一个“,”号。

从第2个位置开始截取，截取的长度是角色列表中第一个角色的长度。