SQL Server 2008, ISIN Check Function

Create a function to Check the ISIN 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 ISIN. This is expanded SQL code using the documentation on the Wikipedia article on the link. You will need to create the following table to hold all of the country information.

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

Create Table

CREATE TABLE SecuritiesIssuer(

SecuritiesIssuerID intIDENTITY(1,1) NOT NULL CONSTRAINT PK_SecuritiesIssuerID PRIMARY KEY,

SecuritiesIssuerShort NVARCHAR(2) NULL,

SecuritiesIssuerCountry NVARCHAR(50) NULL,

SecuritiesIssuerDescription NVARCHAR(max) NULL)

ISIN Countries

ShortCountryDescription
AFAFGHANISTANNULL
AXÅLAND ISLANDSNULL
ALALBANIANULL
DZALGERIANULL
ASAMERICAN SAMOANULL
ADANDORRANULL
AOANGOLANULL
AIANGUILLANULL
AQANTARCTICANULL
AGANTIGUA AND BARBUDANULL
ARARGENTINANULL
AMARMENIANULL
AWARUBANULL
AUAUSTRALIANULL
ATAUSTRIANULL
AZAZERBAIJANNULL
BSBAHAMASNULL
BHBAHRAINNULL
BDBANGLADESHNULL
BBBARBADOSNULL
BYBELARUSNULL
BEBELGIUMNULL
BZBELIZENULL
BJBENINNULL
BMBERMUDANULL
BTBHUTANNULL
BOBOLIVIA, PLURINATIONAL STATE OFNULL
BQBONAIRE, SINT EUSTATIUS AND SABANULL
BABOSNIA AND HERZEGOVINANULL
BWBOTSWANANULL
BVBOUVET ISLANDNULL
BRBRAZILNULL
IOBRITISH INDIAN OCEAN TERRITORYNULL
BNBRUNEI DARUSSALAMNULL
BGBULGARIANULL
BFBURKINA FASONULL
BIBURUNDINULL
KHCAMBODIANULL
CMCAMEROONNULL
CACANADANULL
CVCAPE VERDENULL
KYCAYMAN ISLANDSNULL
CFCENTRAL AFRICAN REPUBLICNULL
TDCHADNULL
CLCHILENULL
CNCHINANULL
CXCHRISTMAS ISLANDNULL
CCCOCOS (KEELING) ISLANDSNULL
COCOLOMBIANULL
KMCOMOROSNULL
CGCONGONULL
CDCONGO, THE DEMOCRATIC REPUBLIC OF THENULL
CKCOOK ISLANDSNULL
CRCOSTA RICANULL
CICÔTE D’IVOIRENULL
HRCROATIANULL
CUCUBANULL
CWCURAÇAONULL
CYCYPRUSNULL
CZCZECH REPUBLICNULL
DKDENMARKNULL
DJDJIBOUTINULL
DMDOMINICANULL
DODOMINICAN REPUBLICNULL
ECECUADORNULL
EGEGYPTNULL
SVEL SALVADORNULL
GQEQUATORIAL GUINEANULL
ERERITREANULL
EEESTONIANULL
ETETHIOPIANULL
FKFALKLAND ISLANDS (MALVINAS)NULL
FOFAROE ISLANDSNULL
FJFIJINULL
FIFINLANDNULL
FRFRANCENULL
GFFRENCH GUIANANULL
PFFRENCH POLYNESIANULL
TFFRENCH SOUTHERN TERRITORIESNULL
GAGABONNULL
GMGAMBIANULL
GEGEORGIANULL
DEGERMANYNULL
GHGHANANULL
GIGIBRALTARNULL
GRGREECENULL
GLGREENLANDNULL
GDGRENADANULL
GPGUADELOUPENULL
GUGUAMNULL
GTGUATEMALANULL
GGGUERNSEYNULL
GNGUINEANULL
GWGUINEA-BISSAUNULL
GYGUYANANULL
HTHAITINULL
HMHEARD ISLAND AND MCDONALD ISLANDSNULL
VAHOLY SEE (VATICAN CITY STATE)NULL
HNHONDURASNULL
HKHONG KONGNULL
HUHUNGARYNULL
ISICELANDNULL
ININDIANULL
IDINDONESIANULL
IRIRAN, ISLAMIC REPUBLIC OFNULL
IQIRAQNULL
IEIRELANDNULL
IMISLE OF MANNULL
ILISRAELNULL
ITITALYNULL
JMJAMAICANULL
JPJAPANNULL
JEJERSEYNULL
JOJORDANNULL
KZKAZAKHSTANNULL
KEKENYANULL
KIKIRIBATINULL
KPKOREA, DEMOCRATIC PEOPLE’S REPUBLIC OFNULL
KRKOREA, REPUBLIC OFNULL
KWKUWAITNULL
KGKYRGYZSTANNULL
LALAO PEOPLE’S DEMOCRATIC REPUBLICNULL
LVLATVIANULL
LBLEBANONNULL
LSLESOTHONULL
LRLIBERIANULL
LYLIBYANULL
LILIECHTENSTEINNULL
LTLITHUANIANULL
LULUXEMBOURGNULL
MOMACAONULL
MKMACEDONIA, THE FORMER YUGOSLAV REPUBLIC OFNULL
MGMADAGASCARNULL
MWMALAWINULL
MYMALAYSIANULL
MVMALDIVESNULL
MLMALINULL
MTMALTANULL
MHMARSHALL ISLANDSNULL
MQMARTINIQUENULL
MRMAURITANIANULL
MUMAURITIUSNULL
YTMAYOTTENULL
MXMEXICONULL
FMMICRONESIA, FEDERATED STATES OFNULL
MDMOLDOVA, REPUBLIC OFNULL
MCMONACONULL
MNMONGOLIANULL
MEMONTENEGRONULL
MSMONTSERRATNULL
MAMOROCCONULL
MZMOZAMBIQUENULL
MMMYANMARNULL
NANAMIBIANULL
NRNAURUNULL
NPNEPALNULL
NLNETHERLANDSNULL
NCNEW CALEDONIANULL
NZNEW ZEALANDNULL
NINICARAGUANULL
NENIGERNULL
NGNIGERIANULL
NUNIUENULL
NFNORFOLK ISLANDNULL
MPNORTHERN MARIANA ISLANDSNULL
NONORWAYNULL
OMOMANNULL
PKPAKISTANNULL
PWPALAUNULL
PSPALESTINIAN TERRITORY, OCCUPIEDNULL
PAPANAMANULL
PGPAPUA NEW GUINEANULL
PYPARAGUAYNULL
PEPERUNULL
PHPHILIPPINESNULL
PNPITCAIRNNULL
PLPOLANDNULL
PTPORTUGALNULL
PRPUERTO RICONULL
QAQATARNULL
RERÉUNIONNULL
ROROMANIANULL
RURUSSIAN FEDERATIONNULL
RWRWANDANULL
BLSAINT BARTHÉLEMYNULL
SHSAINT HELENA, ASCENSION AND TRISTAN DA CUNHANULL
KNSAINT KITTS AND NEVISNULL
LCSAINT LUCIANULL
MFSAINT MARTIN (FRENCH PART)NULL
PMSAINT PIERRE AND MIQUELONNULL
VCSAINT VINCENT AND THE GRENADINESNULL
WSSAMOANULL
SMSAN MARINONULL
STSAO TOME AND PRINCIPENULL
SASAUDI ARABIANULL
SNSENEGALNULL
RSSERBIANULL
SCSEYCHELLESNULL
SLSIERRA LEONENULL
SGSINGAPORENULL
SXSINT MAARTEN (DUTCH PART)NULL
SKSLOVAKIANULL
SISLOVENIANULL
SBSOLOMON ISLANDSNULL
SOSOMALIANULL
ZASOUTH AFRICANULL
GSSOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDSNULL
SSSOUTH SUDANNULL
ESSPAINNULL
LKSRI LANKANULL
SDSUDANNULL
SRSURINAMENULL
SJSVALBARD AND JAN MAYENNULL
SZSWAZILANDNULL
SESWEDENNULL
CHSWITZERLANDNULL
SYSYRIAN ARAB REPUBLICNULL
TWTAIWAN, PROVINCE OF CHINANULL
TJTAJIKISTANNULL
TZTANZANIA, UNITED REPUBLIC OFNULL
THTHAILANDNULL
TLTIMOR-LESTENULL
TGTOGONULL
TKTOKELAUNULL
TOTONGANULL
TTTRINIDAD AND TOBAGONULL
TNTUNISIANULL
TRTURKEYNULL
TMTURKMENISTANNULL
TCTURKS AND CAICOS ISLANDSNULL
TVTUVALUNULL
UGUGANDANULL
UAUKRAINENULL
AEUNITED ARAB EMIRATESNULL
GBUNITED KINGDOMNULL
USUNITED STATESNULL
UMUNITED STATES MINOR OUTLYING ISLANDSNULL
UYURUGUAYNULL
UZUZBEKISTANNULL
VUVANUATUNULL
VEVENEZUELA, BOLIVARIAN REPUBLIC OFNULL
VNVIET NAMNULL
VGVIRGIN ISLANDS, BRITISHNULL
VIVIRGIN ISLANDS, U.S.NULL
WFWALLIS AND FUTUNANULL
EHWESTERN SAHARANULL
YEYEMENNULL
ZMZAMBIANULL
ZWZIMBABWENULL
XSInternationalNULL

SQL

ALTER FUNCTION CheckISIN(@ISIN NVARCHAR(12))

RETURNS INT AS BEGIN

DECLARE @Check INT

--Check Digit not available

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

SET @Check=-1

RETURN @Check

END

--Check country not available

IF (SELECT COUNT(*) FROM SecuritiesIssuer WHERE SecuritiesIssuerShort=LEFT(@ISIN,2))=BEGIN

SET @Check=-2

RETURN @Check

END

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

SET @Check=-3

RETURN @Check

END

 

DECLARE @Data NVARCHAR(30)=LEFT(@ISIN,11)

DECLARE @MultOdd INT= 1, @MultEve INT= 1

DECLARE @Sum INT=0,@StrOdd NVARCHAR(60)='',@StrEve NVARCHAR(60)=''

DECLARE @Letter INT,@Text VARCHAR(1)

      SET @Letter =PATINDEX('%[A-Z]%',@Data)

      SET @Text=SUBSTRING(@Data,@Letter,1)

      BEGIN

      WHILE @Letter>0

      BEGIN

      SET @Data=STUFF(@Data,@Letter,1,ASCII(UPPER(@Text))-55)

     

      SET @Letter =PATINDEX('%[A-Z]%',@Data)

      SET @Text=SUBSTRING(@Data,@Letter,1)

      END

END

--Get Length Multiplier

IF LEN(@Data)%2=0

BEGIN

SET @MultEve = 2;

END

ELSE

SET @MultOdd = 2;

 

SET @Letter = 1

WHILE @Letter<=LEN(@DataBEGIN

IF @Letter%2=BEGIN

SET @StrOdd=@StrOdd+CONVERT(VARCHAR(60),CONVERT(INT,SUBSTRING(@Data,@Letter,1))*@MultOdd);

END

ELSE

SET @StrEve=@StrEve+CONVERT(VARCHAR(60),CONVERT(INT,SUBSTRING(@Data,@Letter,1))*@MultEve);

SET @Letter=@Letter+1

END

SET @Data=@StrOdd+@StrEve

 

SET @Letter = 1

WHILE @Letter<=LEN(@DataBEGIN

SET @Sum=@Sum+CONVERT(INT,SUBSTRING(@Data,@Letter,1))

SET @Letter=@Letter+1

END

 

IF LEN(@ISIN)<>12 BEGIN

SET @Check=-4

RETURN @Check

END

 

IF ISNULL(@Check,0)=BEGIN

SET @Check =(SELECT (CASE WHEN RIGHT(@ISIN,1)=CONVERT(VARCHAR(1),(10-(@Sum%10))%10THEN 1 ELSE 0 END));

END

 

RETURN @Check

END

Warning!

This will only check the format of the ISIN, not whether it actually exists and is valid.
Ousia Logo