Saturday, November 20, 2010

Oracle PL/SQL: Convert ISBN10 to ISBN13 (EAN)

Wrote this because of vendors that still are using ISBN10. Not as optimized as it could be... But should get the job done.

create or replace
FUNCTION "ISBN10TO13" (
ISBN10 IN VARCHAR2
) RETURN VARCHAR2 IS

v_isbntemp VARCHAR2(13) := '';
v_isbn13 VARCHAR2(26) := '';
v_checksum NUMBER := 0;
v_digit NUMBER := 0;
v_pos NUMBER := 1;

BEGIN
v_isbntemp := '978' || substr(ISBN10,0,9);

WHILE (v_pos <= 12) LOOP
v_digit := substr(v_isbntemp,(v_pos),1);
IF (MOD(v_pos,2) = 0)
THEN v_checksum := v_checksum + (v_digit * 3);
ELSE v_checksum := v_checksum + (v_digit * 1);
END IF;
v_pos := v_pos + 1;
END LOOP;

v_checksum := 10 - MOD(v_checksum,10);
IF (v_checksum = 10)
THEN v_checksum := 0;
END IF;

v_isbn13 := v_isbntemp || v_checksum;

RETURN v_isbn13;
END ISBN10TO13;

2 comments:

Unknown said...

You wrote a good article,and could you write something about java,i am learning this and i want to know more.

Senthil Kumaran said...

hi, i have a Question,

The above function even converts the bad/invalid isbn10 to isbn13 .
how to get rid of that.

For Example :
0062310699 -- bad
0062310690 --correct
the function converts both