Check SEDOL format with an SQL Function

Create a function to Check the SEDOL number format in SQL Server

Context

This code was created as part of an import routine to cleanse data coming in from external sources, where there were all sorts of characters that shouldn't have been there.

This one checks the format of an SEDOL. This is expanded SQL code using the documentation on the Wikipedia article on the link.

This is part of three linked articles which will check ISIN, SEDOL and CUSIP identifiers.

SQL

ALTER FUNCTION CheckSEDOL(@SEDOL NVARCHAR(20))RETURNS INT AS BEGINDECLARE @Check INTIF RIGHT(@SEDOL,1) NOT BETWEEN '0' AND '9' BEGINSET @Check=-1RETURN @CheckENDIF PATINDEX('%[^0-Z]%',@SEDOL)>0 BEGINSET @Check=-3RETURN @CheckEND
DECLARE @Sum INT=0,@Letter INT=1,@Char VARCHAR(1)WHILE @LetterBEGINSET @Char=SUBSTRING(@SEDOL,@Letter,1)SET @Sum=@Sum+((SELECT (CASE WHEN @Char BETWEEN '0' AND '9' THEN @Char ELSE ASCII(UPPER(@Char))-55 END))*(CASE @Letter WHEN 1 THEN 1 WHEN 2 THEN 3 WHEN 3 THEN 1 WHEN 4 THEN 7 WHEN 5 THEN 3 WHEN 6 THEN 9 WHEN 7 THEN 1 ELSE 0 END))SET @Letter=@Letter+1ENDSET @Sum=(10 - @Sum%10)% 10
IF LEN(@SEDOL)<>7 BEGINSET @Check=-4RETURN @CheckEND
IF RIGHT(@SEDOL,1) BETWEEN '0' AND '9' BEGINSET @Check =(CASE WHEN RIGHT(@SEDOL,1)=@Sum THEN 1 ELSE 0 END)ENDRETURN @CheckEND

Warning!

This will only check the format of the SEDOL, not whether it actually exists.

Context

SQL

Warning!

Further Reading