RegExr in SQL password check Procedure-CodePudding

I have an procedure to check password in SQL server, but its wrong, can someone explain and help me ? If the password not contain 1 upper letter, 1 number and minium 8 charracter, this procedure will return an message.

CREATE OR ALTER PROCEDURE PaswordCompatibilityCheck
@Password nvarchar(200),
@ConfirmPassword nvarchar(200)
AS
BEGIN
    if @Password NOT LIKE '%(?=.*\d)(?=.*[A-Z]).{8,}%'
    BEGIN
         SELECT 'The password must contain at least 1 capital letter and 1 number, minimum of 8 characters' as message, 'Error' as type
    RETURN -- We need return here for exit the procedure.
    END
END
----EXEC PaswordCompatibilityCheck 'PassWord101','PassWord101'

CodePudding user response：

SQL Server does not support native regex in this way. However, its LIKE operator is enhanced and does support a bit of regex functionality. You may try the following version:

CREATE OR ALTER PROCEDURE PaswordCompatibilityCheck
    @Password nvarchar(200),
    @ConfirmPassword nvarchar(200)
AS
    BEGIN
    IF NOT @Password LIKE '%[0-9]%' OR NOT @Password LIKE '%[A-Z]%' OR
           LEN(@Password) < 8
        BEGIN
            SELECT 'The password must contain at least 1 capital letter and 1 number, minimum of 8 characters' as message, 'Error' as type
    RETURN -- We need return here for exit the procedure.
    END
END

----EXEC PaswordCompatibilityCheck 'PassWord101','PassWord101'