--以下为sql server中的函数.--select ascii('A') , ascii('Z') , ascii('0') , ascii('9')create function dbo.my_func (@s varchar(100)) RETURNS varchar(100) begin declare @rtn as varchar(100) set @rtn = '' declare @i as int set @i = 1 while @i <= len(@s) begin if (ascii(substring(@s , @i , 1)) between 65 and 90) or (ascii(substring(@s , @i , 1)) between 48 and 57) set @rtn = @rtn + '_' + substring(@s , @i , 1) else set @rtn = @rtn + substring(@s , @i , 1) set @i = @i + 1 end RETURN @rtn end goselect dbo.my_func('aaAbs4')drop function dbo.my_func/*
------------------------ aa_Abs_4(所影响的行数为 1 行) */
--创建函数 CREATE OR REPLACE FUNCTION ADDXIAHUAXIAN(STR IN VARCHAR2) RETURN VARCHAR2 IS len integer; i integer; cha varchar2(1); restr varchar2(1024); BEGIN restr:=''; len:=length(str); for i in 1..len loop begin cha:=substr(str,i,1); if ascii(cha) between 65 AND 90 THEN restr:=restr||'_'||cha; else restr:=restr||cha; end if;
end; end loop; return (restr); END; --测试 select addxiahuaxian('aABB123') from dual;
select addxiahuaxian('aABB123') from dual; yf520gn的代码执行结果是:a_A_B_B123 数字前面的下划线还没加上去,我要的结果是a_A_B_B_123
begin
declare @rtn as varchar(100)
set @rtn = ''
declare @i as int
set @i = 1
while @i <= len(@s)
begin
if (ascii(substring(@s , @i , 1)) between 65 and 90) or (ascii(substring(@s , @i , 1)) between 48 and 57)
set @rtn = @rtn + '_' + substring(@s , @i , 1)
else
set @rtn = @rtn + substring(@s , @i , 1)
set @i = @i + 1
end
RETURN @rtn
end
goselect dbo.my_func('aaAbs4')drop function dbo.my_func/*
------------------------
aa_Abs_4(所影响的行数为 1 行)
*/
--创建函数
CREATE OR REPLACE FUNCTION ADDXIAHUAXIAN(STR IN VARCHAR2) RETURN VARCHAR2
IS
len integer;
i integer;
cha varchar2(1);
restr varchar2(1024);
BEGIN
restr:='';
len:=length(str);
for i in 1..len loop
begin
cha:=substr(str,i,1);
if ascii(cha) between 65 AND 90 THEN
restr:=restr||'_'||cha;
else restr:=restr||cha;
end if;
end;
end loop;
return (restr);
END;
--测试
select addxiahuaxian('aABB123') from dual;
yf520gn的代码执行结果是:a_A_B_B123
数字前面的下划线还没加上去,我要的结果是a_A_B_B_123