27,579
社区成员
发帖
与我相关
我的任务
分享用SQL写一个存储过程10进制转33进制代码
急求10进制转33进制代码。
就是我想在SQL中创建一个存储过程“T10TO33”可以实现将10进制的数据转换成33进制。
33进制规则是：1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,J,K,L,M,N,P,Q,R,S,T,V,W,X,Y,Z,0
字母：I、O、U不用，其余正常流水，哪位大神帮忙写一个，谢谢
就是我想在SQL中创建一个存储过程“T10TO33”可以实现将10进制的数据转换成33进制。
33进制规则是：1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,J,K,L,M,N,P,Q,R,S,T,V,W,X,Y,Z,0
字母：I、O、U不用，其余正常流水，哪位大神帮忙写一个，谢谢
...全文
请发表友善的回复…
发表回复
道素 2017-02-09
- 打赏
- 举报
试试这个转换的结果和你要求的是否一致：
CREATE FUNCTION fn_ConvertDecNumberToNewBase
( @DecNumber INT,
@NewBase INT
)
RETURNS VARCHAR(120)
AS
BEGIN
DECLARE @vUppers VARCHAR(23)
DECLARE @vLowers VARCHAR(23)
DECLARE @vNumbers VARCHAR(10)
DECLARE @bitUppercaseOnly BIT
DECLARE @bitNoNumbers BIT
SELECT @bitUppercaseOnly = 0
SELECT @bitNoNumbers = 0
SELECT @vUppers = 'ABCDEFGHJKLMNPQRSTVWXYZ'
SELECT @vLowers = 'abcdefghjklmnpqrstvwxyz'
SELECT @vNumbers= '0123456789'
DECLARE @vCharactersInBase VARCHAR(62)
SELECT @vCharactersInBase = @vUppers
IF @bitNoNumbers = 0
BEGIN
SELECT @vCharactersInBase = @vNumbers + @vCharactersInBase
END
IF @bitUppercaseOnly = 0
BEGIN
SELECT @vCharactersInBase = @vCharactersInBase + @vLowers
END
IF LEN(@vCharactersInBase) < @NewBase
BEGIN
RETURN 'Error Occured'
END
DECLARE @vNewNumber VARCHAR(120)
SELECT @vNewNumber = ''
IF @DecNumber=0 RETURN 0
WHILE @DecNumber <> 0
BEGIN
SELECT @vNewNumber = SUBSTRING(@vCharactersInBase, (@DecNumber % @NewBase) + 1, 1) + @vNewNumber
SELECT @DecNumber = @DecNumber / @NewBase
END
RETURN @vNewNumber
END
GO
DECLARE @i INT =0
WHILE @i<9999
BEGIN
PRINT 'Dec No:'+LTRIM(@i)+' To ' +LTRIM(dbo.fn_ConvertDecNumberToNewBase(@i,33))
SET @i+=1
END
Dec No:0 To 0
Dec No:1 To 1
Dec No:2 To 2
Dec No:3 To 3
Dec No:4 To 4
Dec No:5 To 5
Dec No:6 To 6
Dec No:7 To 7
Dec No:8 To 8
Dec No:9 To 9
Dec No:10 To A
Dec No:11 To B
Dec No:12 To C
Dec No:13 To D
Dec No:14 To E
Dec No:15 To F
Dec No:16 To G
Dec No:17 To H
Dec No:18 To J
Dec No:19 To K
Dec No:20 To L
Dec No:21 To M
Dec No:22 To N
Dec No:23 To P
Dec No:24 To Q
Dec No:25 To R
Dec No:26 To S
Dec No:27 To T
Dec No:28 To V
Dec No:29 To W
Dec No:30 To X
Dec No:31 To Y
Dec No:32 To Z
Dec No:33 To 10
Dec No:34 To 11
Dec No:35 To 12
Dec No:36 To 13
Dec No:37 To 14
Dec No:38 To 15
Dec No:39 To 16
Dec No:40 To 17
Dec No:41 To 18
Dec No:42 To 19
Dec No:43 To 1A
Dec No:44 To 1B
Dec No:45 To 1C
Dec No:46 To 1D
Dec No:47 To 1E
Dec No:48 To 1F
Dec No:49 To 1G
Dec No:50 To 1H
Dec No:51 To 1J
Dec No:52 To 1K
Dec No:53 To 1L
Dec No:54 To 1M
Dec No:55 To 1N
Dec No:56 To 1P
Dec No:57 To 1Q
Dec No:58 To 1R
Dec No:59 To 1S
Dec No:60 To 1T
Dec No:61 To 1V
Dec No:62 To 1W
Dec No:63 To 1X
Dec No:64 To 1Y
Dec No:65 To 1Z
tcmakebest 2017-02-08
- 打赏
- 举报
自从知道CLR,这种东西用C#写就好了.
Andy-W 2017-02-08
- 打赏
- 举报
try:
use tempdb
go
if object_id('T10TO33') Is not null
Drop Proc T10TO33
Go
create procedure T10TO33(
@input_int int,
@output nvarchar(1024) output
)
as
set nocount on
declare @tb_tmp as table(id int identity(0,1) primary key,radix char(1))
;with cte as (
select top (10) convert(varchar(2),row_number() over(order by getdate())-1) as radix from sys.columns
union all
select top (26) char(row_number() over(order by getdate())+64) as radix from sys.columns
)
insert into @tb_tmp(radix)
select radix from cte where radix not in ('I','O','U')
set @output=''
while(1=1)
begin
set @output=Convert(nvarchar(1024),Case (@input_int%33) When 0 Then Rtrim(@input_int%33) Else (select radix from @tb_tmp where id=@input_int%33) End+@output)
set @input_int=@input_int/33
if @input_int=0 break
end
go
--测试：
declare @reuslt nvarchar(1024)
exec T10TO33 32,@reuslt output
print @reuslt --Z
exec T10TO33 33,@reuslt output
print @reuslt --10
exec T10TO33 34,@reuslt output
print @reuslt --11
exec T10TO33 99,@reuslt output
print @reuslt --30
exec T10TO33 100,@reuslt output
print @reuslt --31
zbdzjx 2017-02-08
- 打赏
- 举报
是不能这样改，@T33变量相当于是一个数组，表示数字0~32对应的是哪个字符。
如果把0放到最后，那原来的结果10就变成21了。(1变成了2，0变成了1)
Sam大哥 2017-02-08
- 打赏
- 举报
我把这里改了，是不是不可以这样改？
set @T33='123456789ABCDEFGHJKLMNPQRSTVWXYZ0'
zbdzjx 2017-02-08
- 打赏
- 举报
呵呵，Delphi没什么人用了，找不到理解的回复
我刚试了，好象不对，
exec T10TO33 33
----
结果：21[/quote]
我试过了，结果是10啊。
Sam大哥 2017-02-08
- 打赏
- 举报
呵呵，Delphi没什么人用了，找不到理解的回复
我刚试了，好象不对，
exec T10TO33 33
----
结果：21
zbdzjx 2017-02-08
- 打赏
- 举报
从Delphi版块问到这里了啊。
ALTER PROCEDURE [dbo].[T10TO33]
@T10 varchar(100)
AS
BEGIN
declare @Result varchar(100)
declare @T33 varchar(100)
declare @T10n int
set @Result=''
set @T33='0123456789ABCDEFGHJKLMNPQRSTVWXYZ'
if @T10 is not null and ISNUMERIC(@T10)=1
begin
set @T10n=CONVERT(int, @T10)
while(1=1)
begin
if @T10n<33
begin
set @Result=SUBSTRING(@T33, @T10n+1, 1)+@Result
break
end
else
begin
set @Result=SUBSTRING(@T33, @T10n%33+1, 1)+@Result
set @T10n=@T10n/33
end
end
end
select @Result
END
二月十六 2017-02-08
- 打赏
- 举报
网上找的，效果不错：
--删除表和存储过程
DROP TABLE xl;
DROP PROC p_s_a;
--新建表和存储过程
GO
CREATE TABLE xl ( id INT, xl VARCHAR(200) );
GO
CREATE PROC p_s_a ( @p_a VARCHAR(200) OUT )
AS
BEGIN
DECLARE @i INT ,
@y INT ,
@i1 INT ,
@i2 INT ,
@minid INT;
DECLARE @p_a1 VARCHAR(200);
IF ( SELECT COUNT(*)
FROM xl
) = 0
BEGIN
SET @i = 48;
SET @y = 1;
SET @i1 = 48;
SET @i2 = 48;
WHILE @i < 91
BEGIN
IF @i = 58
SET @i = 65;
IF @i = 73
SET @i = 74;
IF @i = 79
SET @i = 80;
IF @i = 81
SET @i = 82;
WHILE @i1 < 91
BEGIN
IF @i1 = 58
SET @i1 = 65;
IF @i1 = 73
SET @i1 = 74;
IF @i1 = 79
SET @i1 = 80;
IF @i1 = 81
SET @i1 = 82;
WHILE @i2 < 91
BEGIN
IF @i2 = 58
SET @i2 = 65;
IF @i2 = 73
SET @i2 = 74;
IF @i2 = 79
SET @i2 = 80;
IF @i2 = 81
SET @i2 = 82;
INSERT INTO xl
( id, xl )
VALUES ( @y,
CHAR(@i) + CHAR(@i1)
+ CHAR(@i2) );
SET @i2 = @i2 + 1;
SET @y = @y + 1;
END
SET @i2 = 48;
SET @i1 = @i1 + 1;
END
SET @i1 = 48;
SET @i = @i + 1;
END
END
SELECT @minid = MIN(id)
FROM xl;
SELECT @p_a = xl
FROM xl
WHERE id = @minid;
DELETE FROM xl
WHERE id = @minid;
PRINT 'the sn is ' + @p_a;
END
--测试
DECLARE @aa VARCHAR(200);
EXEC p_s_a @aa OUT;
SELECT @aa;
xdashewan 2017-02-08
- 打赏
- 举报
去搜索一个其他语言写的十进制与自定义进制转换的代码，然后自己转成存储过程
Andy-W 2017-02-08
- 打赏
- 举报
n年前写的一个，楼主可以参考下：
Declare @x nvarchar(1024) --要转换的数据
Declare @y int --要转换的数据
Declare @sys int --x进制
Set @x='30'
Set @sys=21 --@sys=21表示二十一进制
--x进制转换成10进制
;With
T1 As(
Select
X=Stuff(Reverse(@x),1,1,''),
Y=Convert(int,Case Isnumeric(Right(@x,1)) When 0 Then ASCII(Upper(Right(@x,1)))-55 Else Right(@x,1) End*Power(@sys,0)),
L=Convert(int,1)
Union All
Select
X=Stuff(X,1,1,''),
Y=Convert(int,Case Isnumeric(Left(X,1)) When 0 Then ASCII(Upper(Left(X,1)))-55 Else Left(X,1) End*Power(@sys,L)),
L=L+Convert(int,1)
From T1
Where X>''
)
Select [10进制-->X进制]=SUM(Y) From T1
--10进制换成x进制转
Set @y=32
Set @sys=2 --@sys=2表示二进制
;With
T1 As(
Select
X=Convert(int,@y/@sys),
Y=Convert(nvarchar(1024),Case (@y%@sys)/10 When 0 Then Rtrim(@y%@sys) Else Char(@y%@sys%10+65) End),
L=Convert(int,1)
Union All
Select
X=X/@sys,
Y=Convert(nvarchar(1024),Case (X%@sys)/10 When 0 Then Rtrim(X%@sys) Else Char(X%@sys%10+65) End+Y),
L=Convert(int,L+1)
From T1 Where X>0
)
Select Top 1 [x进制-->10进制]=Y From T1 Order By L Desc
/*
10进制-->X进制
-----------
63
x进制-->10进制
---------------------
100000
*/
