自动生成字符型订单单号

--创建自定义函数
create function f_orderid()
returns varchar(10)
as
begin
return(select 'ISBN1122'+(case
   when len(max(right([id],2))+1)>1
   then cast(max(right([id],2))+1 as varchar(10))
   else '0'+cast(isnull(max(right([id],2)),0)+1 as varchar(10))
          end)
 from T2)
end

--将自定义函数应用到表中

create table T2
(
[id] varchar(20) default dbo.f_orderid(),
[name] varchar(20)
)

--插入测试数据

insert into T2([name])
select '语文'
union all
select '数学'
union all
select '英语'

--查看结果

select *
from T2

 

转自：http://blog.csdn.net/feegle_develop/article/details/5251338