Hello Developers,

In this demonstration, we will learn how to make custom Password Encode Decode functions in MS SQL Server. When we use MS SQL Server as the database management system, we can make our own custom Password encode decode function.

First, we will learn what is encode and decode? Basically, Encode means to convert something from one system of communication into another and the Decode is just the opposite.

Custom Function of Password Encode

Here we will take the password and count the length of the password.

DECLARE
	@Pos	AS INT,
	@decode AS VARCHAR(35)

SET @decode = ''
SET @Pos = LEN(@xpassword)

After that, we will create a for-loop. In the loop we will take each character one by one and find the ASCII of that character and add some integer value.

ASCII(SUBSTRING(@xpassword, @Pos,1)) + 50

Then we turn the value by using CHAR() function.

CHAR(ASCII(SUBSTRING(@xpassword, @Pos,1)) + 50)

If we complete the function the code will look like this:

CREATE FUNCTION [dbo].[PasswordEecode] 
(	
	@xpassword	AS VARCHAR(35)
)
RETURNS VARCHAR(35)
AS
BEGIN
	DECLARE
		@Pos	AS INT,
		@encode AS VARCHAR(35)

	SET @encode = ''
	SET @Pos = LEN(@xpassword)

	WHILE @Pos <> 0
	BEGIN
		SET @encode = @encode + CHAR(ASCII(SUBSTRING(@xpassword, @Pos,1)) + 50)

		SELECT @Pos = @Pos -1
	END
	RETURN @encode
END

If we execute the function in the MS SQL server, then the function will be found in the [Database]/Programmability/Functions/Scaler-valued Functions/dbo.PasswordEecode.

Custom Function of Password Decode

Here we will take the password and count the length of the password. Same as before.

DECLARE
	@Pos	AS INT,
	@decode AS VARCHAR(35)

SET @decode = ''
SET @Pos = LEN(@xpassword)

After that, we will create a for-loop. In the loop, we will take each character one by one and find the ASCII of that character and subtract some integer value.

ASCII(SUBSTRING(@xpassword, @Pos,1)) - 50

Then we turn the value by using CHAR() function.

CHAR(ASCII(SUBSTRING(@xpassword, @Pos,1)) - 50)

If we complete the function the code will look like this:

CREATE FUNCTION [dbo].[PasswordDecode] 
(	
	@xpassword	AS VARCHAR(35)
)
RETURNS VARCHAR(35)
AS
BEGIN
	DECLARE
		@Pos	AS INT,
		@decode AS VARCHAR(35)

	SET @decode = ''
	SET @Pos = LEN(@xpassword)

	WHILE @Pos <> 0
	BEGIN
		SET @decode = @decode + CHAR(ASCII(SUBSTRING(@xpassword, @Pos,1)) - 50)

		SELECT @Pos = @Pos -1
	END
	RETURN @decode
END

If we execute the function in the MS SQL server, then the function will be found in the [Database]/Programmability/Functions/Scaler-valued Functions/dbo.PasswordDecode.

That's all for today. Hope these functions might help you in the journey of development. 

Read More: How To Add try...catch with DB::transaction() in Laravel