SQL - Split different column names display with separator

In some situation, we need to show separator for firstname and middlename. This can be comma if there some values.

Lets take sample of employees data as below

With EmployeeCTE AS (

    SELECT * FROM (VALUES (1, 'Balaji', NULL),

    (2, 'Siva', NULL),

    (3,'Balaji', 'Prasad'),

    (4,NULL,'Raj'),

    (5,NULL,NULL),

    (6,'Karthi','m')) as E(Sno,FirstName,MiddleName) )

Now query this to get comma separate names out of this

      

SELECT    

p.sno,p.firstname,p.middlename,

IsNull(p.FirstName, '')

+ CASE

 WHEN p.MiddleName = ''

 THEN ''

 WHEN (IsNull(p.FirstName, '') <> '' and IsNull(p.MiddleName, '') <> '')

 THEN IsNull(', ' + p.MiddleName, '')

 ELSE IsNull(p.MiddleName, '') END As Name

FROM  EmployeeCTE AS p

Now the output will come as comma separated if there values present in firstname or lastname

sno	firstname	middlename	Name
1	Balaji	NULL	Balaji
2	Siva	NULL	Siva
3	Balaji	Prasad	Balaji, Prasad
4	NULL	Raj	Raj
5	NULL	NULL
6	Karthi	m	Karthi, m

OTP Mechanism in Asp.Net core

OTP mechanism can be done using different algorithms like TOTP or HOTP. To use it we have inbuilt plugins available,

I have used Otp.Net and TOTP algorithm for this purpose, we can refer it from https://github.com/kspearrin/Otp.NET

Step 1: Refer library from Nuget "Otp.Net" https://www.nuget.org/packages/Otp.NET

Step 2: Create TOTP Object

var emailToSend ="balajisrmv@gmail.com";

var secretKey = Encoding.ASCII.GetBytes(emailToSend);

var TotpObj = new Totp(secretKey, step: 60); //set step for 60 secs for OTP expiration

var otpString = TotpObj.ComputeTotp();

//Send to email, you can customize this to however needed.

emailService.SendEmail(toAddress: emailToSend, subject: "OTP Subject", body: "Your otp code is: " + otpString);

Step 3: Send this otpString to any channel like Email or SMS as your covenient

Step 4: Create seperate action method to validate input OTP code from user

public IActionResult OnPostVerifyAuthCodeAsync(string OtpCode)

        {

   var emailToSend ="balajisrmv@gmail.com";

   var secretKey = Encoding.ASCII.GetBytes(emailToSend);

   var TotpObj = new Totp(secretKey, step: 60); //set step for 60 secs for OTP expiration            

bool otpValid = TotpObj.VerifyTotp(OtpCode, out long timeStepMatched, new VerificationWindow(2, 2));

            if (otpValid)

            {

               

//OTP is valid proceed your business logic            

            }

            else

            {

               //OTP is invalid throw error

     }

            return Page();

        }