SQL Server 2008, ISIN kontrola funkcji
Ten sprawdza format na ISIN . To jest rozszerzony kod SQL przy użyciu dokumentacji na artykuł na link. Musisz utworzyć następującą tabelę do przechowywania wszystkich informacji o kraju.
Jest to część trzech połączonych artykułów, które sprawdzi ISIN, SEDOL i CUSIP identyfikatorów.
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
| Short | Country | Description |
| AF | AFGHANISTAN | NULL |
| AX | ÅLAND ISLANDS | NULL |
| AL | ALBANIA | NULL |
| DZ | ALGERIA | NULL |
| AS | AMERICAN SAMOA | NULL |
| AD | ANDORRA | NULL |
| AO | ANGOLA | NULL |
| AI | ANGUILLA | NULL |
| AQ | ANTARCTICA | NULL |
| AG | ANTIGUA AND BARBUDA | NULL |
| AR | ARGENTINA | NULL |
| AM | ARMENIA | NULL |
| AW | ARUBA | NULL |
| AU | AUSTRALIA | NULL |
| AT | AUSTRIA | NULL |
| AZ | AZERBAIJAN | NULL |
| BS | BAHAMAS | NULL |
| BH | BAHRAIN | NULL |
| BD | BANGLADESH | NULL |
| BB | BARBADOS | NULL |
| BY | BELARUS | NULL |
| BE | BELGIUM | NULL |
| BZ | BELIZE | NULL |
| BJ | BENIN | NULL |
| BM | BERMUDA | NULL |
| BT | BHUTAN | NULL |
| BO | BOLIVIA, PLURINATIONAL STATE OF | NULL |
| BQ | BONAIRE, SINT EUSTATIUS AND SABA | NULL |
| BA | BOSNIA AND HERZEGOVINA | NULL |
| BW | BOTSWANA | NULL |
| BV | BOUVET ISLAND | NULL |
| BR | BRAZIL | NULL |
| IO | BRITISH INDIAN OCEAN TERRITORY | NULL |
| BN | BRUNEI DARUSSALAM | NULL |
| BG | BULGARIA | NULL |
| BF | BURKINA FASO | NULL |
| BI | BURUNDI | NULL |
| KH | CAMBODIA | NULL |
| CM | CAMEROON | NULL |
| CA | CANADA | NULL |
| CV | CAPE VERDE | NULL |
| KY | CAYMAN ISLANDS | NULL |
| CF | CENTRAL AFRICAN REPUBLIC | NULL |
| TD | CHAD | NULL |
| CL | CHILE | NULL |
| CN | CHINA | NULL |
| CX | CHRISTMAS ISLAND | NULL |
| CC | COCOS (KEELING) ISLANDS | NULL |
| CO | COLOMBIA | NULL |
| KM | COMOROS | NULL |
| CG | CONGO | NULL |
| CD | CONGO, THE DEMOCRATIC REPUBLIC OF THE | NULL |
| CK | COOK ISLANDS | NULL |
| CR | COSTA RICA | NULL |
| CI | CÔTE D’IVOIRE | NULL |
| HR | CROATIA | NULL |
| CU | CUBA | NULL |
| CW | CURAÇAO | NULL |
| CY | CYPRUS | NULL |
| CZ | CZECH REPUBLIC | NULL |
| DK | DENMARK | NULL |
| DJ | DJIBOUTI | NULL |
| DM | DOMINICA | NULL |
| DO | DOMINICAN REPUBLIC | NULL |
| EC | ECUADOR | NULL |
| EG | EGYPT | NULL |
| SV | EL SALVADOR | NULL |
| GQ | EQUATORIAL GUINEA | NULL |
| ER | ERITREA | NULL |
| EE | ESTONIA | NULL |
| ET | ETHIOPIA | NULL |
| FK | FALKLAND ISLANDS (MALVINAS) | NULL |
| FO | FAROE ISLANDS | NULL |
| FJ | FIJI | NULL |
| FI | FINLAND | NULL |
| FR | FRANCE | NULL |
| GF | FRENCH GUIANA | NULL |
| PF | FRENCH POLYNESIA | NULL |
| TF | FRENCH SOUTHERN TERRITORIES | NULL |
| GA | GABON | NULL |
| GM | GAMBIA | NULL |
| GE | GEORGIA | NULL |
| DE | GERMANY | NULL |
| GH | GHANA | NULL |
| GI | GIBRALTAR | NULL |
| GR | GREECE | NULL |
| GL | GREENLAND | NULL |
| GD | GRENADA | NULL |
| GP | GUADELOUPE | NULL |
| GU | GUAM | NULL |
| GT | GUATEMALA | NULL |
| GG | GUERNSEY | NULL |
| GN | GUINEA | NULL |
| GW | GUINEA-BISSAU | NULL |
| GY | GUYANA | NULL |
| HT | HAITI | NULL |
| HM | HEARD ISLAND AND MCDONALD ISLANDS | NULL |
| VA | HOLY SEE (VATICAN CITY STATE) | NULL |
| HN | HONDURAS | NULL |
| HK | HONG KONG | NULL |
| HU | HUNGARY | NULL |
| IS | ICELAND | NULL |
| IN | INDIA | NULL |
| ID | INDONESIA | NULL |
| IR | IRAN, ISLAMIC REPUBLIC OF | NULL |
| IQ | IRAQ | NULL |
| IE | IRELAND | NULL |
| IM | ISLE OF MAN | NULL |
| IL | ISRAEL | NULL |
| IT | ITALY | NULL |
| JM | JAMAICA | NULL |
| JP | JAPAN | NULL |
| JE | JERSEY | NULL |
| JO | JORDAN | NULL |
| KZ | KAZAKHSTAN | NULL |
| KE | KENYA | NULL |
| KI | KIRIBATI | NULL |
| KP | KOREA, DEMOCRATIC PEOPLE’S REPUBLIC OF | NULL |
| KR | KOREA, REPUBLIC OF | NULL |
| KW | KUWAIT | NULL |
| KG | KYRGYZSTAN | NULL |
| LA | LAO PEOPLE’S DEMOCRATIC REPUBLIC | NULL |
| LV | LATVIA | NULL |
| LB | LEBANON | NULL |
| LS | LESOTHO | NULL |
| LR | LIBERIA | NULL |
| LY | LIBYA | NULL |
| LI | LIECHTENSTEIN | NULL |
| LT | LITHUANIA | NULL |
| LU | LUXEMBOURG | NULL |
| MO | MACAO | NULL |
| MK | MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF | NULL |
| MG | MADAGASCAR | NULL |
| MW | MALAWI | NULL |
| MY | MALAYSIA | NULL |
| MV | MALDIVES | NULL |
| ML | MALI | NULL |
| MT | MALTA | NULL |
| MH | MARSHALL ISLANDS | NULL |
| MQ | MARTINIQUE | NULL |
| MR | MAURITANIA | NULL |
| MU | MAURITIUS | NULL |
| YT | MAYOTTE | NULL |
| MX | MEXICO | NULL |
| FM | MICRONESIA, FEDERATED STATES OF | NULL |
| MD | MOLDOVA, REPUBLIC OF | NULL |
| MC | MONACO | NULL |
| MN | MONGOLIA | NULL |
| ME | MONTENEGRO | NULL |
| MS | MONTSERRAT | NULL |
| MA | MOROCCO | NULL |
| MZ | MOZAMBIQUE | NULL |
| MM | MYANMAR | NULL |
| NA | NAMIBIA | NULL |
| NR | NAURU | NULL |
| NP | NEPAL | NULL |
| NL | NETHERLANDS | NULL |
| NC | NEW CALEDONIA | NULL |
| NZ | NEW ZEALAND | NULL |
| NI | NICARAGUA | NULL |
| NE | NIGER | NULL |
| NG | NIGERIA | NULL |
| NU | NIUE | NULL |
| NF | NORFOLK ISLAND | NULL |
| MP | NORTHERN MARIANA ISLANDS | NULL |
| NO | NORWAY | NULL |
| OM | OMAN | NULL |
| PK | PAKISTAN | NULL |
| PW | PALAU | NULL |
| PS | PALESTINIAN TERRITORY, OCCUPIED | NULL |
| PA | PANAMA | NULL |
| PG | PAPUA NEW GUINEA | NULL |
| PY | PARAGUAY | NULL |
| PE | PERU | NULL |
| PH | PHILIPPINES | NULL |
| PN | PITCAIRN | NULL |
| PL | POLAND | NULL |
| PT | PORTUGAL | NULL |
| PR | PUERTO RICO | NULL |
| QA | QATAR | NULL |
| RE | RÉUNION | NULL |
| RO | ROMANIA | NULL |
| RU | RUSSIAN FEDERATION | NULL |
| RW | RWANDA | NULL |
| BL | SAINT BARTHÉLEMY | NULL |
| SH | SAINT HELENA, ASCENSION AND TRISTAN DA CUNHA | NULL |
| KN | SAINT KITTS AND NEVIS | NULL |
| LC | SAINT LUCIA | NULL |
| MF | SAINT MARTIN (FRENCH PART) | NULL |
| PM | SAINT PIERRE AND MIQUELON | NULL |
| VC | SAINT VINCENT AND THE GRENADINES | NULL |
| WS | SAMOA | NULL |
| SM | SAN MARINO | NULL |
| ST | SAO TOME AND PRINCIPE | NULL |
| SA | SAUDI ARABIA | NULL |
| SN | SENEGAL | NULL |
| RS | SERBIA | NULL |
| SC | SEYCHELLES | NULL |
| SL | SIERRA LEONE | NULL |
| SG | SINGAPORE | NULL |
| SX | SINT MAARTEN (DUTCH PART) | NULL |
| SK | SLOVAKIA | NULL |
| SI | SLOVENIA | NULL |
| SB | SOLOMON ISLANDS | NULL |
| SO | SOMALIA | NULL |
| ZA | SOUTH AFRICA | NULL |
| GS | SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS | NULL |
| SS | SOUTH SUDAN | NULL |
| ES | SPAIN | NULL |
| LK | SRI LANKA | NULL |
| SD | SUDAN | NULL |
| SR | SURINAME | NULL |
| SJ | SVALBARD AND JAN MAYEN | NULL |
| SZ | SWAZILAND | NULL |
| SE | SWEDEN | NULL |
| CH | SWITZERLAND | NULL |
| SY | SYRIAN ARAB REPUBLIC | NULL |
| TW | TAIWAN, PROVINCE OF CHINA | NULL |
| TJ | TAJIKISTAN | NULL |
| TZ | TANZANIA, UNITED REPUBLIC OF | NULL |
| TH | THAILAND | NULL |
| TL | TIMOR-LESTE | NULL |
| TG | TOGO | NULL |
| TK | TOKELAU | NULL |
| TO | TONGA | NULL |
| TT | TRINIDAD AND TOBAGO | NULL |
| TN | TUNISIA | NULL |
| TR | TURKEY | NULL |
| TM | TURKMENISTAN | NULL |
| TC | TURKS AND CAICOS ISLANDS | NULL |
| TV | TUVALU | NULL |
| UG | UGANDA | NULL |
| UA | UKRAINE | NULL |
| AE | UNITED ARAB EMIRATES | NULL |
| GB | UNITED KINGDOM | NULL |
| US | UNITED STATES | NULL |
| UM | UNITED STATES MINOR OUTLYING ISLANDS | NULL |
| UY | URUGUAY | NULL |
| UZ | UZBEKISTAN | NULL |
| VU | VANUATU | NULL |
| VE | VENEZUELA, BOLIVARIAN REPUBLIC OF | NULL |
| VN | VIET NAM | NULL |
| VG | VIRGIN ISLANDS, BRITISH | NULL |
| VI | VIRGIN ISLANDS, U.S. | NULL |
| WF | WALLIS AND FUTUNA | NULL |
| EH | WESTERN SAHARA | NULL |
| YE | YEMEN | NULL |
| ZM | ZAMBIA | NULL |
| ZW | ZIMBABWE | NULL |
| XS | International | NULL |
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))=0 BEGIN
SET @Check=-2
RETURN @Check
END
IF PATINDEX('%[^0-Z]%',@ISIN)>0 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(@Data) BEGIN
IF @Letter%2=1 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(@Data) BEGIN
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)=0 BEGIN
SET @Check =(SELECT (CASE WHEN RIGHT(@ISIN,1)=CONVERT(VARCHAR(1),(10-(@Sum%10))%10) THEN 1 ELSE 0 END));
END
RETURN @Check
END
