SQL_scripts

1
--1 ВАРИАНТ числа в одной системе
2
--указать два числа в одной системе и базу системы (от 2 до 36)
3

4
declare
5
	@number1 varchar(100) = 'f1f'
6
	,@number2 varchar(100) = '1f1'
7
	,@base int = 16
8

9
set nocount on
10

11
declare
12
	@array varchar(100) = '0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z'
13
	,@separator varchar(1) = ','
14
	,@dec bigint
15
	,@out varchar(100)
16

17
if len(@number2) > len(@number1)
18
begin
19
	set @out = @number1
20
	set @number1 = @number2
21
	set @number2 = @out
22
	set @out = null
23
end
24

25
declare @digits table (num int primary key)
26

27
;with ten as (
28
	select n
29
	from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t(n)
30
),cte as (
31
	select row_number() over(order by (select null)) as n
32
	from
33
		ten as t1
34
		cross join ten as t2
35
		cross join ten as t3
36
)
37
insert into @digits (num)
38
select n from cte
39

40
declare @dict table (
41
	ord int
42
	,val varchar(1) primary key
43
)
44

45
insert into @dict (ord, val)
46
select top (@base)
47
	row_number() over(order by (select null)) - 1
48
	,cast(s.[value] as varchar(1))
49
from string_split(@array, @separator) as s
50

51
;with c1 as (
52
	select top (len(@number1))
53
		t.num
54
		,substring(@number1, row_number() over(order by t.num), 1) as symb
55
	from @digits as t
56
	order by t.num
57
), c2 as (
58
	select top (len(@number2))
59
		t.num
60
		,substring(@number2, row_number() over(order by t.num), 1) as symb
61
	from @digits as t
62
	order by t.num
63
), t1 as (
64
	select
65
		t.symb
66
		,row_number() over(order by t.num desc) as ord
67
		,d.ord as val
68
	from
69
		c1 as t
70
		inner join @dict as d
71
			on d.val = t.symb
72
), t2 as (
73
	select
74
		t.symb
75
		,row_number() over(order by t.num desc) as ord
76
		,d.ord as val
77
	from
78
		c2 as t
79
		inner join @dict as d
80
			on d.val = t.symb
81

82
), s as (
83
select
84
    isnull(t1.val, 0) as val1
85
    ,isnull(t2.val, 0) as val2
86
	,t1.ord
87
from
88
	t1
89
	full join t2
90
		on t1.ord = t2.ord
91
union all
92
select
93
	0
94
	,0
95
	,(select max(ord) + 1 from t1)
96
), r as (
97
	select
98
		s.ord
99
		,s.val1
100
		,s.val2
101
		,(s.val1 + s.val2) / @base as Tnf
102
		,((s.val1 + s.val2) % @base) as newVal
103
	from s
104
	where s.ord = 1
105
	union all
106
	select
107
		s.ord
108
		,s.val1
109
		,s.val2
110
		,((s.val1 + s.val2 + r.Tnf) / @base) as Tnf
111
		,((s.val1 + s.val2 + r.Tnf) % @base) as newVal
112
	from
113
		s
114
		inner join r
115
			on r.ord + 1 = s.ord
116
), w as (
117
	select
118
		r.ord
119
		,d.val
120
	from
121
		r
122
		inner join @dict as d
123
			on d.ord = r.newVal
124
)
125
select @out = string_agg(val, '') within group (order by ord desc)
126
from w
127

128
select @out
129

130
go
131

132
--2 ВАРИАНТ число в любой системе, а инкремент в десятичной
133

134
declare
135
	@number varchar(100) = 'fff' --число в системе база которой указывается в @base
136
	,@base int = 16
137
	,@dec_inc int = 5 -- десятичный инкремент
138

139
--
140
set nocount on
141

142
declare
143
	@array varchar(100) = '0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z'
144
	,@separator varchar(1) = ','
145
	,@dec bigint
146
	,@out varchar(100)
147

148
declare @digits table (num int)
149

150
;with ten as (
151
	select n
152
	from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t(n)
153
),cte as (
154
	select row_number() over(order by (select null)) as n
155
	from
156
		ten as t1
157
		cross join ten as t2
158
		cross join ten as t3
159
)
160
insert into @digits (num)
161
select n from cte
162

163
declare @dict table (
164
	ord int
165
	,val varchar(1)
166
)
167

168
insert into @dict (ord, val)
169
select top (@base)
170
	row_number() over(order by (select null)) - 1
171
	,cast(s.[value] as varchar(1))
172
from string_split(@array, @separator) as s
173

174
;with t as (
175
	select top (len(@number))
176
		t.num
177
		,substring(@number, row_number() over(order by t.num), 1) as val
178
	from @digits as t
179
	order by t.num
180
), s as ( 
181
	select d.ord * power(@base, row_number() over(order by t.num desc) - 1) as val
182
	from
183
		t
184
		inner join @dict as d
185
			on t.val = d.val
186
)
187
select @dec = sum(val)
188
from s
189

190
set @dec += @dec_inc
191

192
;with rec as (
193
	select
194
		@dec / @base as [dec]
195
		,(select val from @dict where ord = @dec % @base) as val
196
		,0 as ord
197
	union all
198
	select
199
		r.[dec] / @base
200
		,d.val
201
		,r.ord + 1
202
	from
203
		rec as r
204
		inner join @dict as d
205
			on d.ord = r.[dec] % @base
206
	where not (r.[dec] / @base = 0 and r.[dec] % @base = 0)
207
)
208
select @out = string_agg(val, '') within group (order by ord desc)
209
from rec
210

211
select @out as result
212

213

214