SEDOL Check Correct Format Function in SQL

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 BEGIN

DECLARE @Check INT

IF RIGHT(@SEDOL,1) NOT BETWEEN '0' AND '9' BEGIN

SET @Check=-1

RETURN @Check

END

IF PATINDEX('%[^0-Z]%',@SEDOL)>BEGIN

SET @Check=-3

RETURN @Check

END

 

DECLARE @Sum INT=0,@Letter INT=1,@Char VARCHAR(1)

WHILE @Letter<LEN(@SEDOL)

BEGIN

SET @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+1

END

SET @Sum=(10 - @Sum%10)% 10

 

IFLEN(@SEDOL)<>BEGIN

SET @Check=-4

RETURN @Check

END

 

IF RIGHT(@SEDOL,1) BETWEEN '0' AND '9' BEGIN

SET @Check =(CASE WHEN RIGHT(@SEDOL,1)=@Sum THEN 1 ELSE 0 END)

END

RETURN @Check

END

Warning!

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