原系統(tǒng)品牌編碼為三位,因用到999,所以必須考慮采用字母,現(xiàn)將以前用的笨笨辦法寫出來(lái),日后有更好的辦法會(huì)改進(jìn).
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
一位一位判斷,如果是數(shù)字位,看是否為九,超過(guò)九就自動(dòng)加一,然后轉(zhuǎn)換成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