SQL> select substr(name,1,length(name)-1)||chr(ascii(substr(name,-1)) -1) 2 from test where id in (12,13);SUBSTR(NAME,1,LENGTH(NAME)-1)||CHR(ASCII(S ------------------------------------------ hl2321 12hhicSQL> select name from test where id in(12,13);NAME -------------------- hl2322 12hhidSQL>
SQL> select substr(name,1,length(rtrim(name,'0123456789')))|| 2 to_char(to_number(substr(name,length(rtrim(name,'0123456789')) +1))-1) from a 3 ;SUBSTR(NAME,1,LENGTH(RTRIM(NAME,'0123456789')))||TO_CHAR(TO_NUMBER(SUBSTR(NAME,L -------------------------------------------------------------------------------- df3222 ef907 c99SQL> select name from a;NAME -------------------- df3223 ef908 c100SQL> 大致就是这样 然后就需要自己对特殊情况进行改进了
非常感谢啊那对于是纯数字串的情况这个能不能加在一个SQL中进行呢
写一个函数,就方便在一个SQL语句中使用了. create or replace function mySub(str varchar2) return varchar2 is sstr varchar2(16); ilen integer; itemp integer; ichar varchar2(1); begin ilen:=length(str); sstr:=''; for i in 0..ilen loop ichar:=substr(str,ilen-i,1); itemp:=i; if ascii(ichar)>=ascii('0') and ascii(ichar)<=ascii('9') then sstr:=concat(ichar,sstr); else exit; end if; end loop; sstr:=to_char(to_number(sstr)-1); sstr:=concat(substr(str,ilen-itemp,length(str)-length(sstr)),sstr); return sstr; end;--测试: select mySub('a001') from dual;
转换成10进制,再减,再转换成16进制-----十六进制转换十进制----------------- select sum(data1) from (select (CASE upper(substr('1122AF', rownum, 1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE substr('1122AF', rownum, 1) END) * power(16, length('1122AF') - rownum) data1 from dual connect by rownum <= length('1122AF'))
2 from test where id in (12,13);SUBSTR(NAME,1,LENGTH(NAME)-1)||CHR(ASCII(S
------------------------------------------
hl2321
12hhicSQL> select name from test where id in(12,13);NAME
--------------------
hl2322
12hhidSQL>
不过似乎再考虑一下末位是0的情况就好了
例如四舍五入,若是那样的话 可能会比较麻烦写PLSQL都要写很多 都未必能够考虑全你可以把自己的需求好好整理下 然后在发上来其实若是写PLSQL的话 就和编程没有什么区别,你若是了解SQL的函数的话 就可以自己动手去写了。
前面是字母,后面是数字,加减只对数字如:YY1000加减1后就为:YY1001,YY999这样应该明确些了
SQL> select substr(name,1,length(rtrim(name,'0123456789')))||
2 to_char(to_number(substr(name,length(rtrim(name,'0123456789')) +1))-1) from a
3 ;SUBSTR(NAME,1,LENGTH(RTRIM(NAME,'0123456789')))||TO_CHAR(TO_NUMBER(SUBSTR(NAME,L
--------------------------------------------------------------------------------
df3222
ef907
c99SQL> select name from a;NAME
--------------------
df3223
ef908
c100SQL> 大致就是这样 然后就需要自己对特殊情况进行改进了
create or replace function mySub(str varchar2) return varchar2
is
sstr varchar2(16);
ilen integer;
itemp integer;
ichar varchar2(1);
begin
ilen:=length(str);
sstr:='';
for i in 0..ilen loop
ichar:=substr(str,ilen-i,1);
itemp:=i;
if ascii(ichar)>=ascii('0') and ascii(ichar)<=ascii('9') then
sstr:=concat(ichar,sstr);
else
exit;
end if;
end loop;
sstr:=to_char(to_number(sstr)-1);
sstr:=concat(substr(str,ilen-itemp,length(str)-length(sstr)),sstr);
return sstr;
end;--测试: select mySub('a001') from dual;
应该是:concat(substr(str,1,ilen-itemp),sstr);
select sum(data1)
from (select (CASE upper(substr('1122AF', rownum, 1))
WHEN 'A' THEN '10'
WHEN 'B' THEN '11'
WHEN 'C' THEN '12'
WHEN 'D' THEN '13'
WHEN 'E' THEN '14'
WHEN 'F' THEN '15'
ELSE substr('1122AF', rownum, 1)
END) * power(16, length('1122AF') - rownum) data1
from dual
connect by rownum <= length('1122AF'))
然后用UNION来把他们在联合起来就可以了大部分的函数也就上面的那么几个 用法几乎相同,你自己研究下吧自己研究出来的东西记得扎实 如果在遇到什么问题 在问