原系统品牌编码为三位,因用到999,所以必须考虑采用字母,现将以前用的笨笨办法写出来,日后有更好的办法会改进.
create function uf_getasciirowno()
returns varchar(3) as
begin
declare @rowid varchar(3),
@rowid2 varchar(1),
@rowid3 varchar(1),
@rowid4 varchar(1)
select @rowid = isnull(max(code),'000') from t_model
select @rowid2 = subString(@rowid,1,1)
select @rowid3 = subString(@rowid,2,1)
select @rowid4 = subString(@rowid,3,1)
if @rowid4 in ('z') and @rowid3 not in ('z') and @rowid2 not in ('z')
select @rowid = @rowid2+dbo.uf_maxascii(@rowid3)+'0'
if @rowid4 in ('z') and @rowid3 in ('z')
select @rowid = dbo.uf_maxascii(@rowid2)+'0'+'0'
if @rowid4 not in ('z') and @rowid3 not in ('z') and @rowid2 not in ('z')
select @rowid = @rowid2+@rowid3+dbo.uf_maxascii(@rowid4)
if @@error<>0
select @rowid=''
return @rowid
end
一位一位判断,如果是数字位,看是否为九,超过九就自动加一,然后转换成ASC码..............
create function uf_maxascii(@rowid varchar(1))
returns varchar(1)
as
begin
declare @rowid4 char(1)
if @rowid in ('0','1','2','3','4','5','6','7','8')
select @rowid4 = @rowid+1
if @rowid in ('9')
select @rowid4 = 'a'
if @rowid in ('z')
select @rowid4 = '0'
if @rowid in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y')
select @rowid4=char(ascii(@rowid)+1)
if @@error<>0
select @rowid='0'
return @rowid4
end