반응형
(1) 기본 시리얼 번호 생성
SELECT NEWID() AS Code FROM sys.sysobjects SELECT TOP 1000000 NEWID() AS Code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c |
(2) 쿠폰 시리얼 번호 생성
DECLARE @v_Quantity INT -- 발행 쿠폰 수 DECLARE @v_CouponLength INT -- 쿠폰 자리 수 SET @v_Quantity = 10000 SET @v_CouponLength = 12 SELECT m.Code FROM ( SELECT TOP(@v_Quantity) LEFT(REPLACE(NEWID(), '-', ''), @v_CouponLength) AS Code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c ) m GROUP BY m.Code |
(3) 4 * 4 쿠폰 생성
DECLARE @v_Quantity INT -- 발행 쿠폰 수 SET @v_Quantity = 10000
FROM ( SELECT TOP(@v_Quantity) LEFT(REPLACE(NEWID(), '-', ''), 16) AS f_code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c ) a GROUP BY a.f_code SELECT SUBSTRING(a.f_code, 1, 4) + '-' + SUBSTRING(a.f_code, 5, 4) + '-' + SUBSTRING(a.f_code, 9, 4) + '-' + SUBSTRING(a.f_code, 13, 4) FROM ( SELECT TOP(@v_Quantity) LEFT(REPLACE(NEWID(), '-', ''), 16) AS f_code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c ) a GROUP BY a.f_code |
(4) 5 * 5 쿠폰 생성
DECLARE @v_Quantity INT -- 발행 쿠폰 수 SET @v_Quantity = 10000
SELECT a.f_code FROM ( SELECT TOP(@v_Quantity) LEFT(REPLACE(NEWID(), '-', ''), 25) AS f_code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c ) a GROUP BY a.f_code SELECT SUBSTRING(a.f_code, 1, 5) + '-' + SUBSTRING(a.f_code, 6, 5) + '-' + SUBSTRING(a.f_code, 11, 5) + '-' + SUBSTRING(a.f_code, 16, 5) + '-' + SUBSTRING(a.f_code, 21, 5) FROM ( SELECT TOP(@v_Quantity) LEFT(REPLACE(NEWID(), '-', ''), 25) AS f_code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c ) a GROUP BY a.f_code |
(5) N * N 쿠폰 생성
DECLARE @v_SQL NVARCHAR(MAX) DECLARE @v_TokenLen INT DECLARE @v_CouponLen INT DECLARE @v_CouponCnt INT DECLARE @v_Index INT SET @v_TokenLen = 5 -- 코드 길이(Ex. 4*4, 5*5) SET @v_CouponLen = @v_TokenLen * @v_TokenLen -- 쿠폰 길이 SET @v_CouponCnt = 100000 -- 생성 쿠폰 개수 SET @v_SQL = 'SELECT (' SET @v_Index = 0 WHILE @v_Index < @v_TokenLen BEGIN IF @v_Index = 0 SET @v_SQL = @v_SQL + 'SUBSTRING(a.f_code, ' + CAST((1 + (@v_Index * @v_TokenLen)) AS NVARCHAR(16)) + ', ' + CAST(@v_TokenLen AS NVARCHAR(16)) + ')' ELSE SET @v_SQL = @v_SQL + ' + ''-'' + SUBSTRING(a.f_code, ' + CAST((1 + (@v_Index * @v_TokenLen)) AS NVARCHAR(16)) + ', ' + CAST(@v_TokenLen AS NVARCHAR(16)) + ')' SET @v_Index = @v_Index + 1 END SET @v_SQL = @v_SQL + ') AS f_serial FROM ( SELECT TOP ' + CAST(@v_CouponCnt AS NVARCHAR(16)) + ' LEFT(REPLACE(NEWID(), ''-'', ''''), ' + CAST(@v_CouponLen AS NVARCHAR(16)) + ') AS f_code FROM sys.sysobjects AS a, sys.sysobjects AS b, sys.sysobjects AS c ) a GROUP BY a.f_code' EXEC SP_EXECUTESQL @v_SQL |
(6) BASE64를 이용한 쿠폰 번호 생성 코드
- dbo.Base64Encode 함수는 '[MSSQL] Base64Encode, Base64Decode 스칼라 함수' 포스트 참조
DECLARE @v_Quantity INT DECLARE @v_Index INT DECLARE @v_Code1 VARCHAR(4) DECLARE @v_Code2 VARCHAR(5) DECLARE @v_Code3 VARCHAR(4) DECLARE @v_Code4 VARCHAR(4) DECLARE @v_Code5 VARCHAR(4) DECLARE @v_Code6 VARCHAR(4) DECLARE @v_Code VARCHAR(30) CREATE TABLE #Coupon ( Code VARCHAR(30) ) SET @v_Index = 10000 SET @v_Quantity = @v_Index + 1000 WHILE @v_Index < @v_Quantity BEGIN SET @v_Code1 = 'A'+ RIGHT(YEAR(GETDATE()), 1) + LEFT(RIGHT(@v_Index, 5), 2) SET @v_Code2 = CONVERT(VARCHAR(3), dbo.Base64Encode(RIGHT(@v_Index, 3))) + LEFT(RIGHT(@v_Index, 3), 1) SET @v_Code3 = CONVERT(VARCHAR(3), LEFT(dbo.Base64Encode(RIGHT(YEAR(GETDATE()), 4)), 3)) + LEFT(RIGHT(@v_Index, 2), 1) SET @v_Code4 = CONVERT(VARCHAR(3), dbo.Base64Encode(RIGHT(@v_Index, 2)))+ RIGHT(@v_Index, 1) INSERT INTO #Coupon(Code) VALUES(@v_Code1 + '-' + @v_Code2 + '-' + @v_Code3 + '-' + @v_Code4) SET @v_Index = @v_Index + 1 END SELECT Code FROM #Coupon GROUP BY Code DROP TABLE #Coupon |
반응형