假設角色表里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個位置開始截取,截取的長度是角色列表中第一個角色的長度。