select REGEXP_SUBSTR(REGEXP_SUBSTR(f,'[a-z,A-Z,0-9]+[^12345678]'),'[a-z,A-Z,0-9]+') as f from ( select '12334zho中国人民' as f from dual union select 'ffsddgdsfdff谁说的发' from dual union select'bghgfhgf12345678' from dual union select'fgdfgdfg' from dual)
--将3楼的sql改为如下: select case when substr(str, length(str) - 7, length(str)) = '12345678' then substr(str, 1, length(str) - 8) else substr(str, 1, length(str) - 4) end from a;
select case when substrb(&string,-8) = '12345678' then substrb(&string,1,lengthb(&string) - 16) else substrb(&string,1,lengthb(&string) - 8) end result_sting from &table;
STR
--------------------------------------------------------------------------------
12334zho中国人民
ffsddgdsfdff谁说的发
bghgfhgf12345678
fgdfgdfg
SQL>
SQL> select case
2 when ascii(substr(str, length(str) - 3, 1)) > 128 and
3 ascii(substr(str, length(str) - 2, 1)) > 128 and
4 ascii(substr(str, length(str) - 1, 1)) > 128 and
5 ascii(substr(str, length(str), 1)) > 128 then
6 substr(str, 1, length(str) - 4)
7 when substr(str, length(str) - 7, length(str)) = '12345678' then
8 substr(str, 1, length(str) - 8)
9 else
10 str
11 end
12 from a;
CASEWHENASCII(SUBSTR(STR,LENGT
--------------------------------------------------------------------------------
12334zho
ffsddgdsfdff
bghgfhgf
fgdfgdfg
下面的是截取字符串=12345678阿楼主可以看这两个函数就可以了。
select '12334zho中国人民' as f from dual union
select 'ffsddgdsfdff谁说的发' from dual union
select'bghgfhgf12345678' from dual union
select'fgdfgdfg' from dual)
--将3楼的sql改为如下:
select case when substr(str, length(str) - 7, length(str)) = '12345678' then
substr(str, 1, length(str) - 8) else substr(str, 1, length(str) - 4) end
from a;
when substrb(&string,-8) = '12345678' then
substrb(&string,1,lengthb(&string) - 16)
else
substrb(&string,1,lengthb(&string) - 8)
end result_sting
from &table;
在这里和大家分享
select decode(substrb(address,-8),'举个例子',substrb(address,1,lengthb(address) - 16) , substrb(address,1,lengthb(address) - 8))
from TABLE