在SQL Server指定的数据库中，有Programmability目录，在这个目录下，有存储过程，有功能函数。

 

 

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER function   [dbo].[fn_10to36]   ( 	@i   int ) returns   varchar(30) as begin 	declare  @r  varchar(30) 	set   @r= '' 	declare  @m  int 	declare  @s  int 	set   @s=@i 	while   @s> =36 	begin 		set   @m=@s   %   36 		set   @r=case   when   @m <10   then   cast(@m   as   varchar)   			else   cast(char(ascii( 'A')+@m-10)   as   varchar)   end+@r 		set   @s=@s/36 	end 		if   @s> 0   or   (@s=0   and   @r= '') 			set   @r=case   when   @s <10   then   cast(@s   as   varchar)   		else   cast(char(ascii( 'A')+@s-10)   as   varchar)   end+@r 	return   @r end 　　

 

比如上述，执行

is not a recognized function name.

 上面报错，因为要加dbo.[function], 

 

select dbo.[fn_10to36_ByJasmine] (101) 即可

 

 

 

下面是36wei转10位

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER function   [dbo].[fn_36to10]   ( @str   varchar(30) ) returns   intas begin declare @returnValue intselect @returnValue = 0declare @str36 varchar(32)declare @subWork varchar(1)declare @workIndex intselect @str36 = '123456789ABCDEFGHIJKLMNOPQRSTUVWSYZ'declare @len intdeclare @i intselect @i = 1select @len = datalength(@str)while (@i <= @len)begin	select @subWork = SUBSTRING(@str, @i, 1) 	select @workIndex = charindex( @subWork, @str36)	select @returnValue = @returnValue + (@workIndex * power(36, @len-@i))	select @i = @i + 1endreturn   @returnValue end

　　

 

前面再带个参数的

CREATE FUNCTION [DBO].[FN_10TO36] (@I INT,@B VARCHAR(10))RETURNS VARCHAR(30)AS BEGINDECLARE @R VARCHAR(30)SET @R=''DECLARE @M INT DECLARE @S INTSET @S=@IWHILE @S>=36BEGINSET @M=@S %36 ----取余SET @R=CASE WHEN @M<10 THEN CAST(@M AS VARCHAR)ELSE CAST(CHAR(ASCII('A')+@M-10)AS VARCHAR) END +@RSET @S=@S/36   -----取值ENDIF @S>0 OR (@S=0 AND @R='')SET @R=CASE WHEN @S<10 THEN CAST(@S AS VARCHAR)ELSE CAST(CHAR(ASCII('A')+@S-10) AS VARCHAR ) END +@RRETURN @B+RIGHT('0000'+@R,5)END