Check CUSIP format with an SQL Function

Create a function to check the CUSIP 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 CUSIP.

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 CheckCUSIP(@CUSIP NVARCHAR(20))RETURNS INT AS BEGINDECLARE @Check INTIF RIGHT(@CUSIP,1) NOT BETWEEN '0' AND '9' BEGINSET @Check=-1RETURN @CheckENDIF PATINDEX('%[^0-Z]%',@CUSIP)>0 BEGINSET @Check=-3RETURN @CheckENDDECLARE @Sum INT=0,@Letter INT=1,@Char VARCHAR(1),@LetVal INT
WHILE @LetterBEGINSET @Char=SUBSTRING(@CUSIP,@Letter,1)SET @LetVal=((SELECT (CASE WHEN @Char BETWEEN '0' AND '9' THEN @Char ELSE ASCII(UPPER(@Char))-55END))*(CASEWHEN @Letter%2=1 THEN 1 ELSE 2 END))
If @LetVal > 9 BEGINSET @LetVal =(@LetVal % 10)+(@LetVal / 10)End
SET @Sum=@Sum+@LetValSET @Letter=@Letter+1END
SET @Sum=(10 -(@Sum % 10))% 10IF LEN(@CUSIP)<>9 BEGINSET @Check=-4RETURN @CheckEND
IF RIGHT(@CUSIP,1) BETWEEN '0' AND '9' BEGINSET @Check =(CASEWHEN RIGHT(@CUSIP,1)=@Sum THEN 1 ELSE 0 END)ENDRETURN @Check
END

Warning!

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

Context

SQL

Warning!

Further Reading