求助，运行自定义函数不是自己想要的结果，找不到原因，谢谢

shimingxiang2022 2022-05-20 14:13:45

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

是新手，一直找不到原因，谢谢大家的帮助！

...全文

73 3 打赏收藏举报

写回复

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

morliz子轩 05-20

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')