create function dbo.EAN_13(@value varchar(13))
returns varchar(13)
as
begin
declare @s1 int ,@s2 int
declare @t table (id int identity(1,1),b bit) insert into @t(b)
select top 13 1 from syscolumns set @value='0'+reverse(@value)
select @s1=sum(cast(substring(@value,id,1) as int))
from @t a
where len(@value)>=id and id%2=0 set @s1=@s1*3 select @s2=sum(cast(substring(@value,id,1) as int))
from @t a
where len(@value)>=id and id>=3 and id%2=1 set @s1=@s1+@s2
return left(reverse(@value),12)+ltrim(10-len(@s1))
end
goselect dbo.EAN_13('234235654652') ean13
godrop function EAN_13/*
ean13
-------------
2342356546528(所影响的行数为 1 行)
*/
returns varchar(13)
as
begin
declare @s1 int ,@s2 int
declare @t table (id int identity(1,1),b bit) insert into @t(b)
select top 13 1 from syscolumns set @value='0'+reverse(@value)
select @s1=sum(cast(substring(@value,id,1) as int))
from @t a
where len(@value)>=id and id%2=0 set @s1=@s1*3 select @s2=sum(cast(substring(@value,id,1) as int))
from @t a
where len(@value)>=id and id>=3 and id%2=1 set @s1=@s1+@s2
return left(reverse(@value),12)+ltrim(10-len(@s1))
end
goselect dbo.EAN_13('234235654652') ean13
godrop function EAN_13/*
ean13
-------------
2342356546528(所影响的行数为 1 行)
*/
解决方案 »
- 求教高手如何把服务器上SQL的数据库备份到本地电脑上
- 问个sql语句的问题,在线等!!!马上给分哦!!
- 求助,哪位大哥有比较完整的sql server 2000的ultraEdit的着色文件,多谢了!
- 如何给表加个序号
- 怎样在win2k professional上安装sql server?
- SQL Server7.0安装问题
- One question about SQL SERVER?
- 在线急求救:::关于reverse字符串函数的一个问题!!!!!!
- String类型最多可以包含多少个字符?
- gg帮帮忙,要选出SQL中一个表前100条记录怎么做????谢谢!!!急
- SQL分类汇总,将汇总数据放在明细最后一条记录,但不另起一行
- 更改了计算机名,sql 2005不能更新了!
set @s='234235654652'
set @ss=''
set @i=0
set @b=0
set @a=0
while @i<=len(@s)
begin
if ((@i+1)%2=1 and (@i+1)>=2)
set @a=@a+cast(substring(@s,@i,1) as int)
if ((@i+1)%2=0 and (@i+1)>=3)
set @b=@b+cast(substring(@s,@i,1) as int)
set @i=@i+1
end
set @c=rtrim(convert( nvarchar(50),3*@a+@b))
select 10-convert(int,substring(@c,len(@c)-1,1)),@a,@b,@c
自己转换为函数或者存储过程
set card_id=dbo.EAN_13(card_id)