[33進数→10進数変換] 誤読しやすいILOを除くA〜Zと数字で採番
/* ********************************************
33進数(※)の文字列を10進数の数値に変換
※33進数 = 0123456789ABCDEFGHJKMNPQRSTUVWXYZ
(誤読しやすい'I','L','O'を除く)
******************************************** */
CREATE FUNCTION [dbo].[fn33DecToDec](
@NUMSTR NVARCHAR(10)
) RETURNS DECIMAL(14, 0)
AS
BEGIN
DECLARE
@DecLength INT = 33, -- ASCII('Z') - ASCII('A') + 1 - 3 + 10,
@OffsetNum0 INT = 48, -- ASCII('0'),
@OffsetNum1 INT = 55, -- ASCII('A') - 10,
@OffsetNum INT
DECLARE
@CUR_CHAR NCHAR(1), -- 変換対象文字
@LEN_NUMSTR INT = LEN(@NUMSTR), -- 変換元文字列の長さ
@RETVAL DECIMAL(14, 0) = 0, -- 自動採番結果
@R_CNT INT
SET @R_CNT = @LEN_NUMSTR
WHILE (@R_CNT > 0)
BEGIN
SET @CUR_CHAR = SUBSTRING(@NUMSTR, ABS(@R_CNT - @LEN_NUMSTR) + 1, 1)
SET @OffsetNum = CASE
WHEN @CUR_CHAR < N'A' THEN @OffsetNum0
WHEN @CUR_CHAR < N'I' THEN @OffsetNum1
WHEN @CUR_CHAR < N'L' THEN @OffsetNum1 + 1
WHEN @CUR_CHAR < N'O' THEN @OffsetNum1 + 2
WHEN @CUR_CHAR <= N'Z' THEN @OffsetNum1 + 3
ELSE @OffsetNum0
END
SET @RETVAL = @RETVAL + (POWER(CONVERT(DECIMAL(14, 0), @DecLength), @R_CNT - 1) * (ASCII(@CUR_CHAR) - @OffsetNum))
SET @R_CNT = @R_CNT - 1
END
RETURN @RETVAL
END
GO