CREATE VIEW [dbo].[View_RandomNumber]
AS
SELECT RAND() AS RANDOMNUMBER
GO
function
CREATE FUNCTION [dbo].[ufn_RandBetween](@BOTTOM INT, @TOP INT)
RETURNS INT
AS
BEGIN
RETURN (SELECT CAST(ROUND((@TOP-@BOTTOM)* RANDOMNUMBER + @BOTTOM,0) AS INTEGER)
FROM View_RandomNumber)
END
GO
총 9자리로 구성되어 있으며, 실행하면 "6263QB802" 이며, 앞에 숫자 4자리 + 대문자 2자리 + 숫자 3자리 = 총 9자리
CREATE FUNCTION [dbo].[ufn_GenerateRandom]()
returns VARCHAR(10)
AS
BEGIN
DECLARE @randInt INT;
DECLARE @NewCharacter VARCHAR(1);
DECLARE @NewPassword VARCHAR(10);
SET @NewPassword='';
--## start of 숫자 4자리
WHILE ( Len(@NewPassword) < 4 )
BEGIN
SELECT @randInt = dbo.ufn_RandBetween(48, 122)
-- 0-9 < = > ? @ A-Z [ \ ] a-z
IF @randInt <= 57
BEGIN
SELECT @NewCharacter = Char(@randInt)
SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
END
END
--## end of 숫자 4자리
--Ensure a lowercase
--SELECT @NewCharacter = Char(dbo.Randbetween(97, 122))
--SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
--Ensure an upper case
--## start of 대문자 2자리
SELECT @NewCharacter = Char(dbo.ufn_RandBetween(65, 90))
SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
SELECT @NewCharacter = Char(dbo.ufn_RandBetween(65, 90))
SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
--## end of 대문자 2자리
-- ## start of 총길이 9자리이며 나머지 자리는 숫자로 채운다.
WHILE ( Len(@NewPassword) < 9 )
BEGIN
SELECT @randInt = dbo.ufn_RandBetween(48, 122)
-- 0-9 < = > ? @ A-Z [ \ ] a-z
IF @randInt <= 57
BEGIN
SELECT @NewCharacter = Char(@randInt)
SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
END
END
-- ## end of 총길이 9자리이며 나머지 자리는 숫자로 채운다.
--Ensure a number
--SELECT @NewCharacter = Char(dbo.ufn_RandBetween(48, 57))
--SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
----Ensure a symbol
--WHILE ( Len(@NewPassword) < 10 )
-- BEGIN
-- SELECT @randInt = dbo.ufn_RandBetween(33, 64)
-- -- ! # $ % & < = > ? @
-- IF @randInt = 33
-- OR ( @randInt >= 35
-- AND @randInt <= 38 )
-- OR ( @randInt >= 60
-- AND @randInt <= 64 )
-- BEGIN
-- SELECT @NewCharacter = Char(@randInt)
-- SELECT @NewPassword = Concat(@NewPassword, @NewCharacter)
-- END
-- END
RETURN( @NewPassword );
END;
GO
실행해 보기
SET @PrizeNumber = 'K' + (SELECT dbo.ufn_GenerateRandom())