你可以用一个小技巧来实现:先将所有逗号替换为逗号后跟一个空格,然后跳过那个额外的前导空格。
SQL> with data as
2 ( select '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N' txt
3 from dual
4 )
5 select regexp_substr(txt,'[^,]+',1,7) seventh_element_wrong
6 , replace(txt,',',', ') with_extra_space_after_comma
7 , regexp_substr(replace(txt,',',', '),'[^,]+',1,7) seventh_element_leading_space
8 , substr(regexp_substr(replace(txt,',',', '),'[^,]+',1,7),2) the_seventh_element
9 from data
10 /
S WITH_EXTRA_SPACE_AFTER_COMMA
-
SEVENTH_ELEMENT_LEADING_S THE_SEVENTH_ELEMENT
1 100016154, 5101884LT00001, , , , , 100000010892100000012655, L, SEI, 5101884LT00001, 1, SL, 3595.03, 00, 2, N, N, G, N
100000010892100000012655 100000010892100000012655
祝好,
罗布。