Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: Converting a formula to SQL help!

    Hello everyone i am new in this forum and i would like your help with a problem that i have been having for some time now: i got a formula in excel that convert ISBN10 to ISBN13, i tried to transit this in SQL but i didnt have much luck only like 10-15% of the ISBN i had is actually correct with my script.

    But let me explain to you a bit better how this formula works. As they say an image equals to a thousand words so check out the attachment to see how this formula works. Furthermore, for an original version of the excel you can download from here (unhide the columns between A and F to see full code).

    Lastly so i make things a tad easier i will post the code i used in SQL so maybe you can help fine-tune it or tell me whats wrong or even better give me the final code if you like ofc (!)


    declare @isbn10 varchar(10),
    @isbn13 varchar(13),
    @i int,
    @chk int

    select @isbn10 = '0123456479'

    select @isbn13 = '978' + left(@isbn10, 9)

    select @i = 1,
    @chk = 0

    while @i <= 9
    begin
    select @chk = @chk + (substring(@isbn10, @i, 1) * case when @i % 2 = 1 then 1 else 3 end)
    select @i = @i + 1
    end

    select @chk = 10 - (@chk % 10)

    select @isbn13 = @isbn13 + convert(char(1), @chk)

    select @isbn13


    Thanks a lot in advance
    Attached Thumbnails Attached Thumbnails exampleg.jpg  

  2. #2
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    The start of the formula in the graphic is Sum(9, 3*7, 8,...). You don't account for this in the SQL procedure. @chk should be initialized to 38 not 0.

    The formula multiplies numbers in the odd position by 3. The Case statement causes the even positioned numbers to be multiplied by 3.

  3. #3
    Join Date
    Feb 2007
    Posts
    33
    Code:
    declare	@isbn10	varchar(10),
    @isbn13	varchar(13),
    @i	int,
    @chk	int
    
    select	@isbn10	= '0443100489'
    
    select	@isbn13	= '978' + left(@isbn10, 9)
    
    SELECT @chk = (38 + 3*LEFT(@isbn10,1) 
    + RIGHT(LEFT(@isbn10,2),1)
    + 3*RIGHT(LEFT(@isbn10,3),1)
    + RIGHT(LEFT(@isbn10,4),1)
    + 3*RIGHT(LEFT(@isbn10,5),1)
    + RIGHT(LEFT(@isbn10,6),1)
    + 3*RIGHT(LEFT(@isbn10,7),1) 
    + RIGHT(LEFT(@isbn10,8),1) 
    + 3*LEFT(RIGHT(@isbn10,2),1))
    
    select	@chk = 10 - (@chk % 10)
    
    select	@isbn13 = @isbn13 + convert(char(1), @chk)
    
    select	@isbn13

    It might not be as pretty but it appear to work.

  4. #4
    Join Date
    May 2010
    Posts
    1
    Hi guys - nice forum you got here!
    Comments by brucevde are very helpfull
    Last edited by vDim; 05-06-10 at 20:11.

  5. #5
    Join Date
    Dec 2011
    Posts
    1

    Smile MySQL implement with a little correct

    use schemaName;
    delimiter //
    CREATE FUNCTION ISBN10to13(isbn10 VARCHAR(50))
    RETURNS VARCHAR(50)
    BEGIN
    # DECLARE isbn10 VARCHAR(10);
    DECLARE isbn13 VARCHAR(13);
    DECLARE i INT;
    DECLARE chk INT;

    IF (LENGTH(ISBN10) > 10) THEN
    RETURN ISBN10;
    ELSE
    SET isbn10=SUBSTRING(ISBN10,1,10);
    END IF;

    # set ISBN10 = '0123456479';
    SET isbn13 = CONCAT('978' , LEFT(isbn10, 9));
    SET i = 1, chk = 0;

    # 9*1+7*3+8*1=38
    set chk = (38 + 3*LEFT(isbn10,1)
    + RIGHT(LEFT(isbn10,2),1)
    + 3*RIGHT(LEFT(isbn10,3),1)
    + RIGHT(LEFT(isbn10,4),1)
    + 3*RIGHT(LEFT(isbn10,5),1)
    + RIGHT(LEFT(isbn10,6),1)
    + 3*RIGHT(LEFT(isbn10,7),1)
    + RIGHT(LEFT(isbn10,8),1)
    + 3*LEFT(RIGHT(isbn10,2),1));

    SET chk = 10 - (chk % 10);
    if (chk<>10) then
    SET isbn13 = concat(isbn13 , CONVERT(chk, CHAR(1)));
    else
    SET isbn13 = concat(isbn13 , '0');
    end if;
    RETURN isbn13;
    END; //
    delimiter ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •