Generate random password using a T-SQL function

Question

I have this code in PLSQL but I am unable to convert it to SQL Server.

I want to generate a random password that contains a mixture of upper and lowercase, numbers, and special characters in T-SQL based on how many of each the user wants to generate.

How would I convert this Oracle code to T-SQL?

CREATE FUNCTION [DBO].[GENPASS]
    (
@p_numbers    INT, 
  @p_specialchar  INT, 
  @p_lowercase    TINYINT, 
  @p_uppercase    INT)
   returns varchar(4000) 
AS
BEGIN
  DECLARE @v_length         float = @p_numbers + @p_specialchar + @p_lowercase + @p_uppercase;
  DECLARE @DECLARE @v_password       varchar(200);
  DECLARE @v_iterations     float = 0;
  DECLARE @DECLARE @v_max_iterations float = 50000;
 
    while 1=1 begin
        SET @v_password = dbms_random.string('p',@v_length);
        SET @v_iterations = @v_iterations + 1;
           exit when (dbo.regexp_count(@v_password,'[a-z]') = @p_lowercase
                 and  dbo.regexp_count(@v_password,'[A-Z]') = @p_uppercase
                 and  dbo.regexp_count(@v_password,'[0-9]') = @p_numbers) 
                 or @v_iterations=@v_max_iterations;
    end;
    if @v_iterations = @v_max_iterations BEGIN
      SET @v_password = '';
RETURN NULL;
    end; 
    return(@v_password);
END; 

Answer 1

This seems to do what you require:

CREATE OR ALTER VIEW dbo.Random
WITH SCHEMABINDING
AS
    SELECT r = RAND(CHECKSUM(NEWID()));
GO
CREATE OR ALTER FUNCTION dbo.GeneratePassword
(
    @lowercase tinyint,
    @uppercase tinyint,
    @numbers tinyint,
    @specialchars tinyint
)
RETURNS 
    varchar(1020)
WITH 
    SCHEMABINDING, 
    RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE
        @random float,
        @UC char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
        @LC char(26) = 'abcdefghijklmnopqrstuvwxyz',
        @NUM char(10) = '0123456789',
        @SC char(32) = '!"#$%&''()*+,-./:;<=>?@[\]^_`{|}~',
        @length integer = 
            CONVERT(integer, @lowercase) + 
            CONVERT(integer, @uppercase) + 
            CONVERT(integer, @numbers) + 
            CONVERT(integer, @specialchars),
        @pos integer,
        @working varchar(1020) = '',
        @return varchar(1020) = '';

    -- Add the required number of random characters of different types
    WHILE @length > 0
    BEGIN
        SELECT @random = R.r FROM dbo.Random AS R;

        IF @lowercase > 0
        BEGIN
            SET @working += SUBSTRING(@LC, CONVERT(integer, 1e0 + @random * 2.6e1), 1);
            SET @lowercase -= 1;
            SET @length -= 1;
        END;

        IF @uppercase > 0
        BEGIN
            SET @working += SUBSTRING(@UC, CONVERT(integer, 1e0 + @random * 2.6e1), 1);
            SET @uppercase -= 1;
            SET @length -= 1;
        END;

        IF @numbers > 0
        BEGIN
            SET @working += SUBSTRING(@NUM, CONVERT(integer, 1e0 + @random * 1e1), 1);
            SET @numbers -= 1;
            SET @length -= 1;
        END;

        IF @specialchars > 0
        BEGIN
            SET @working += SUBSTRING(@SC, CONVERT(integer, 1e0 + @random * 3.2e1), 1);
            SET @specialchars -= 1;
            SET @length -= 1;
        END;
    END;

    -- Shuffle the string into random order
    WHILE @working > ''
    BEGIN
        SELECT @random = R.r FROM dbo.Random AS R;
        SET @pos = 1 + @random * LEN(@working);
        SET @return += SUBSTRING(@working, @pos, 1);
        SET @working = STUFF(@working, @pos, 1, '');
    END;

    RETURN NULLIF(@return, '');
END;

Example call:

SELECT pwd = dbo.GeneratePassword(2, 4, 3, 1);

Random result:

pwd
LrZ45N@6Rn

db<>fiddle demo