CREATE FUNCTION dbo.fnGetISBN
(
	@ISBN VARCHAR(11)
)
RETURNS VARCHAR(13)
AS
BEGIN
	DECLARE	@Index TINYINT,
		@Weight TINYINT,
		@Sum SMALLINT

	SELECT	@Index = LEN(@ISBN),
		@Weight = 2,
		@Sum = 0

	WHILE @Index > 0
		BEGIN
			IF SUBSTRING(@ISBN, @Index, 1) <> '-'
				SELECT	@Sum = @Sum + @Weight * CAST(SUBSTRING(@ISBN, @Index, 1) AS TINYINT),
					@Weight = @Weight + 1

			SET @Index = @Index - 1
		END

	RETURN	CASE @Sum % 11
			WHEN 1 THEN @ISBN + '-X'
			ELSE @ISBN + '-' + CONVERT(CHAR(1), 11 - (@Sum % 11))
		END
END

quote:

---

Originally posted by spirit1

too much time again?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

---