[MSSQL] 지정 길이 랜덤 문자 생성

[MSSQL] 지정 길이 랜덤 문자 생성

 

 

- Key 비슷한 값을 생성하고자 할 때 사용

- 함수에서 뷰 호출하여 생성 ( 뷰 1개, 함수 1개 )

- 함수 @iRandLength 인자 : 랜덤 생성 문자 길이

- 결과예 : SELECT DB명.소유자.CreateRandomString(40) => orJkYMMgrP2zRP3PLtrWEfiIEfKhnoUWtCqdH0pp

 

< 뷰 >

CREATE VIEW [dbo].[RandHelper] AS SELECT RAND( ) AS r GO

 

 

< 함수 >

CREATE FUNCTION [dbo].[CreateRandomString] ( @iRandLength AS SMALLINT ) RETURNS VARCHAR(100) AS BEGIN DECLARE @wPassword VARCHAR(100) DECLARE @wCharacters VARCHAR(100) DECLARE @wCount INT SET @wCharacters = '' -- load up numbers 0 - 9 SET @wCount = 48 WHILE @wCount <=57 BEGIN SET @wCharacters = @wCharacters + Cast(CHAR(@wCount) AS CHAR(1)) SET @wCount = @wCount + 1 END -- load up uppercase letters A - Z SET @wCount = 65 WHILE @wCount <=90 BEGIN SET @wCharacters = @wCharacters + Cast(CHAR(@wCount) as CHAR(1)) SET @wCount = @wCount + 1 END -- load up lowercase letters a - z SET @wCount = 97 WHILE @wCount <=122 BEGIN SET @wCharacters = @wCharacters + Cast(CHAR(@wCount) as CHAR(1)) SET @wCount = @wCount + 1 END SET @wCount = 0 SET @wPassword = '' WHILE @wCount < @iRandLength BEGIN SET @wPassword = @wPassword + SUBSTRING(@wCharacters,CAST(CEILING((SELECT r FROM RandHelper)*LEN(@wCharacters)) AS INT),1) SET @wCount = @wCount + 1 END RETURN @wPassword END