ISIN Validation SQL Function

CREATE FUNCTION dbo.IsValidISIN(@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


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 BEGIN

SET @MultOdd = 2;

SET @Letter = 1;

END


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 BEGIN

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

 END

 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

GO


Comments

Post a Comment

Popular posts from this blog

host

import pandas as pd from datetime import datetime import os # Example DataFrame df_final = pd.DataFrame({     'Name': ['Alice', 'Bob', 'Charlie'],     'Age': [25, 30, 35],     'City': ['New York', 'Los Angeles', 'Chicago'] }) # Generate today's date in 'YYYY-MM-DD' format today = datetime.now().strftime("%Y-%m-%d") # Create a dynamic filename filename = f"{today}_data.csv" # Network drive path (Windows) network_folder = r"\\network_drive\shared_folder" # Full path to save the file full_path = os.path.join(network_folder, filename) # Save DataFrame to CSV try:     df_final.to_csv(full_path, index=False)     print(f"File saved successfully to {full_path}") except Exception as e:     print(f"Error saving file: {e}")
Read more

Steps to create SSH key from git bash

Generating an SSH key in OpenSSH involves a few simple steps. Here’s how you can do it: 1. Open a Terminal If you're on Linux or macOS, you can use the built-in terminal. On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed. 2. Generate the SSH Key Pair Run the following command: bash Copy code ssh-keygen -t rsa -b 4096 -C "your_email@example.com" Here’s what each option means: -t rsa : Specifies the type of key to create, in this case, RSA. -b 4096 : Specifies the number of bits in the key, 4096 bits is a good choice for strong encryption. -C "your_email@example.com" : Adds a label to the key, usually your email address. 3. Choose a File Location After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in ~/.ssh/id_rsa (on Linux or macOS) or in C:\Users\YourUserName\.ssh\id_rsa (on Windows). Press Enter to accept the default location, or specify a diff...
Read more

test

.. https://ubiquis.co.uk/loading-csv-files-with-pdi-metadata-injection-2/ -- Create a temporary table to demonstrate the solution CREATE TABLE YourTable ( ID INT, YourColumn VARCHAR(100) ); -- Insert sample data INSERT INTO YourTable (ID, YourColumn) VALUES (1, '1234_4567_abc'), (2, '789_012_def'), (3, 'xyz'); -- Declare variables for storing results DECLARE @firstPart VARCHAR(100), @secondPart VARCHAR(100); -- Iterate over rows in the table DECLARE @currentID INT = 1; DECLARE @rowCount INT = (SELECT COUNT(*) FROM YourTable); WHILE @currentID <= @rowCount BEGIN -- Retrieve input string from the table DECLARE @inputString VARCHAR(100); SELECT @inputString = YourColumn FROM YourTable WHERE ID = @currentID; -- Check if the delimiter exists in the input string IF CHARINDEX('_', @inputString) > 0 BEGIN -- Find the position of the first occurrence of the delimiter in the input string DECLARE @delimiter...
Read more