[MSSQL] 10進制<->N進制

  • 223
  • 0

0~9及A~Z一共是10+26=36碼, 但0與O及1與I太像所以不用

0~9及A~Z一共是10+26=36碼, 但0與O及1與I太像所以不用

 

/*
SELECT dbo.NBaseToDec(LEFT('0123456789ABCDEFGHJKLMNPQRSTUVWXYZ',34), '2L8')
*/
ALTER FUNCTION [dbo].[NBaseToDec](@BaseFormate VARCHAR(100), @Value VARCHAR(200)) RETURNS DECIMAL(18, 0)
AS   
BEGIN
	DECLARE @Result DECIMAL(18, 0)=0, @Base INT=0, @ValueLen INT=0, @Index INT=0, @FirstChar CHAR(1), @Position INT=0
	SET @Base=LEN(@BaseFormate)
	SET @Value= RTRIM(LTRIM(UPPER(REVERSE(@Value))))
	SET @ValueLen=LEN(@Value)
	WHILE @Index<@ValueLen
	BEGIN
		SET @FirstChar=SUBSTRING(@Value, @Index+1, 1)
		SET @Position=CHARINDEX(@FirstChar, @BaseFormate)-1
		SET @Result=@Position*POWER(@Base, @Index)+@Result
		SET @Index=@Index+1
	END
	RETURN(@Result)
END

 

/*
SELECT dbo.DecToNBase(LEFT('0123456789ABCDEFGHJKLMNPQRSTUVWXYZ',34), 3000)
*/
CREATE FUNCTION [dbo].[DecToNBase](@BaseFormate VARCHAR(100), @Value BIGINT) RETURNS VARCHAR(MAX) 
AS 
BEGIN
	DECLARE @Result VARCHAR(MAX)='', @Base AS INT 
	SET @Base=LEN(@BaseFormate)
	IF @Value < 0 OR @Base < 2 OR @Base > 36 RETURN NULL;
	WHILE @Value > 0
	BEGIN
		SET @Result = SUBSTRING(@BaseFormate, @Value%@Base+1, 1) + @Result
		SET @Value = @Value / @Base;
	END
	RETURN UPPER(@Result);
END