比如这样的字符串,0123456789ABCDEFG加1=0123456789ABCDEFH 再+1
等于 0123456789ABCDEFI如果是0123456789ABCDEFZ加1后等于0123456789ABCDEG0坐等高人、用SQL实现 其他语言不要来了。。
等于 0123456789ABCDEFI如果是0123456789ABCDEFZ加1后等于0123456789ABCDEG0坐等高人、用SQL实现 其他语言不要来了。。
declare @num1 varchar(200), @num2 varchar(200), @res varchar(200)
declare @len1 int, @len2 int, @i int, @n int
declare @c bitset @num1 = '0123456789ABCDEFH'
set @num2 = '1'
set @num1 = reverse(upper(@num1))
set @num2 = reverse(upper(@num2))
set @len1 = len(@num1)
set @len2 = len(@num2)
set @i = 1
set @c = 0
set @res = ''
while ((@i <= @len1) or (@i <= @len2))
begin
set @n = @c if @i <= @len1
begin
if substring(@num1, @i, 1) >= 'A'
set @n = @n + ascii(substring(@num1, @i, 1)) - ascii('A') + 10
else
set @n = @n + ascii(substring(@num1, @i, 1)) - ascii('0')
end if @i <= @len2
begin
if substring(@num2, @i, 1) >= 'A'
set @n = @n + ascii(substring(@num2, @i, 1)) - ascii('A') + 10
else
set @n = @n + ascii(substring(@num2, @i, 1)) - ascii('0')
end if @n >= 36
begin
set @c = 1
set @n = @n - 36
end
else
set @c = 0
if @n >= 10
set @res = @res + char(@n - 10 + ascii('A'))
else
set @res = @res + char(@n + ascii('0')) set @i = @i + 1
endset @res = reverse(@res)select @res
declare @num1 varchar(200), @num2 varchar(200), @res varchar(200)
declare @len1 int, @len2 int, @i int, @n int
declare @c bitset @num1 = 'ZZZZZZZZ'
set @num2 = '1'
set @num1 = reverse(upper(@num1))
set @num2 = reverse(upper(@num2))
set @len1 = len(@num1)
set @len2 = len(@num2)
set @i = 1
set @c = 0
set @res = ''
while ((@i <= @len1) or (@i <= @len2))
begin
set @n = @c if @i <= @len1
begin
if substring(@num1, @i, 1) >= 'A'
set @n = @n + ascii(substring(@num1, @i, 1)) - ascii('A') + 10
else
set @n = @n + ascii(substring(@num1, @i, 1)) - ascii('0')
end if @i <= @len2
begin
if substring(@num2, @i, 1) >= 'A'
set @n = @n + ascii(substring(@num2, @i, 1)) - ascii('A') + 10
else
set @n = @n + ascii(substring(@num2, @i, 1)) - ascii('0')
end if @n >= 36
begin
set @c = 1
set @n = @n - 36
end
else
set @c = 0
if @n >= 10
set @res = @res + char(@n - 10 + ascii('A'))
else
set @res = @res + char(@n + ascii('0')) set @i = @i + 1
end--忘了进位
if @c = 1
set @res = @res + '1'set @res = reverse(@res)select @res
--创建字符转换为数值的函数
create function cton36
(@s varchar(12))
returns bigint
as
begin
declare @n bigint
set @s=upper(@s)
select @n=isnull(@n,0)+
(case when ascii(right(@s,number+1))>=65 then ascii(right(@s,number+1))-55 else ascii(right(@s,number+1))-48 end)*power(convert(bigint,36),number)
from master..spt_values where type='p' and number<len(@s)
return @n
end
go
--创建数值到字符的转换函数
create function ntoc36
(@n bigint)
returns varchar(12)
as
begin
declare @s varchar(12)
set @s=''
while @n>0
begin
set @s=char(case when @n%36<10 then 48+@n%36 else 55+@n%36 end)+@s
set @n=@n/36
end
return @s
end
go
--运算
select dbo.ntoc36(dbo.cton36('f87q3jds9')+dbo.cton36('437dz'))
/*
------------
F87Q7ML68(1 行受影响)
*/
go
--删除自定义函数
drop function dbo.cton36,dbo.ntoc36