CUSIP Check Correct Format Function in SQL

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 BEGIN

DECLARE @Check INT

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

SET @Check=-1

RETURN @Check

END

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

SET @Check=-3

RETURN @Check

END

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

 

WHILE @Letter<LEN(@CUSIP)

BEGIN

SET @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=THEN 1 ELSE 2 END))

 

If @LetVal > 9 BEGIN

SET @LetVal =(@LetVal % 10)+(@LetVal / 10)

End

 

SET @Sum=@Sum+@LetVal

SET @Letter=@Letter+1

END

 

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

 

IF LEN(@CUSIP)<>BEGIN

SET @Check=-4

RETURN @Check

END

 

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

SET @Check =(CASEWHEN RIGHT(@CUSIP,1)=@Sum THEN 1 ELSE 0 END)

END

RETURN @Check

 

END

Warning!

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