34,838
社区成员




CREATE FUNCTION [dbo].[TO_ISBN13](@isbn as NVARCHAR(16))
RETURNS NVARCHAR
as
begin
DECLARE @isbn13 NVARCHAR,
@check_bit int,
@isbn10 int,
@isbn9 int,
@isbn8 int,
@isbn7 int,
@isbn6 int,
@isbn5 int,
@isbn4 int,
@isbn3 int,
@isbn2 int,
@isbnsum1 int
SET @isbn10=CAST(SUBSTRING(@isbn,1,1) as int)
SET @isbn9=CAST(SUBSTRING(@isbn,2,1) as int)
SET @isbn8=CAST(SUBSTRING(@isbn,3,1) as int)
SET @isbn7=CAST(SUBSTRING(@isbn,4,1) as int)
SET @isbn6=CAST(SUBSTRING(@isbn,5,1) as int)
SET @isbn5=CAST(SUBSTRING(@isbn,6,1) as int)
SET @isbn4=CAST(SUBSTRING(@isbn,7,1) as int)
SET @isbn3=CAST(SUBSTRING(@isbn,8,1) as int)
SET @isbn2=CAST(SUBSTRING(@isbn,9,1) as int)
SET @isbnsum1=(9*1+7*3+8*1+@isbn10*3+@isbn9*1+@isbn8*3+@isbn7*1+@isbn6*3+@isbn5*1+@isbn4*3+@isbn3*1+@isbn2*3)
SET @check_bit=10-@isbnsum1%10
SET @isbn13='978'+left(@isbn,9)+cast(@check_bit as NVARCHAR)
RETURN @isbn13
END
如果运行自定义函数:
select TO_ISBN13(7518345528)
正常情况下应该是:9787518345526
但结果出来就一个数字:9
是新手,一直找不到原因,谢谢大家的帮助!
demo:
if exists(select null from sysobjects where name='to_iSbn13')
Drop Function dbo.to_iSbn13
Go
create function dbo.to_iSbn13(@isbn nvarchar(16))
returns nvarchar(max)
as
begin
-- declare @isbn nvarchar(16) ='7518345528'
--前缀1
declare @isbn1 nvarchar(10) ='987'
--取左9位
declare @isbn0 nvarchar(10) = left(@isbn,9)
--剩下计算
declare @isbn10 int,
@isbn9 int,
@isbn8 int,
@isbn7 int,
@isbn6 int,
@isbn5 int,
@isbn4 int,
@isbn3 int,
@isbn2 int
set @isbn10 = cast(substring(@isbn,1,1) as int)
set @isbn9 = cast(substring(@isbn,2,1) as int)
set @isbn8 = cast(substring(@isbn,3,1) as int)
set @isbn7 = cast(substring(@isbn,4,1) as int)
set @isbn6 = cast(substring(@isbn,5,1) as int)
set @isbn5 = cast(substring(@isbn,6,1) as int)
set @isbn4 = cast(substring(@isbn,7,1) as int)
set @isbn3 = cast(substring(@isbn,8,1) as int)
set @isbn2 = cast(substring(@isbn,9,1) as int)
-- select @isbn10,@isbn9,@isbn8,@isbn7,@isbn6,@isbn5,@isbn4,@isbn3,@isbn2
declare @isbnsum1 int
set @isbnsum1 = (9*1 + 7*3 + 8*1 + @isbn10 *3 +@isbn9 *1 +@isbn8 *3 +@isbn7*1 + @isbn6 *3 +@isbn5 *1 +@isbn4 *3
+@isbn3 *1 +@isbn2 *3 )
-- select @isbnsum1
declare @check_bit int = 10 - @isbnsum1 % 10
-- select @check_bit
return @isbn1 + @isbn0 + cast(@check_bit as nvarchar)
-- select @result
end
-- select dbo.to_iSbn13('7518345528')
测试结果: