SQL binary conversion, support for binary conversions within 93

Function: Implement in-SQL internal conversion, support conversion from 2-93, if you need to support other characters, you can customize the @ym variable to achieve expansion

1 -- =============================================2 --Author:bwch3 --Create date:2014 September 30 10:32:474 --Description: Converts a number to a specified input, supports up to 93 binary, and can also be extended within function @ym for other conversions5 -- =============================================6 ALTER FUNCTION [dbo].[Biginttohexstr](7     @value BIGINT,--numbers that need to be converted8     @jz INT =  -    --default 16 binary9     )Ten     RETURNS VARCHAR( -)--return value, the string after which the conversion is made One  as A BEGIN -     --additional @ym can be expanded by expanding the system -     DECLARE @seq VARCHAR( -)--Character Mask the     DECLARE @ym VARCHAR( -)--Mask -     SET @ym = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' [email protected]#$%^&* ()-_=+[]{}\|;: ", . <>/?' -     SET @seq = SUBSTRING(@ym,1,@jz) -     --return value +     DECLARE @result VARCHAR( -) -     --take a number of characters +     DECLARE @digit CHAR(1) A     SET @result = SUBSTRING(@seq, (@value%@jz)+1,1) at      -      while @value > 0 -     BEGIN -         SET @digit = SUBSTRING(@seq, ((@value/@jz)%@jz)+1,1) -         SET @value = @value/@jz -         IF @value <> 0  in             SET @result = @digit + @result -     END  to     RETURN @result + END

1 -- =============================================2 --Author:bwch3 --Create date:2014 September 30 10:36:144 --Description: Converting converted binary into digital5 -- =============================================6 ALTER FUNCTION [dbo].[Hexstrtobigint]7     (8         @value VARCHAR( -),--converted string9         @jz INT =  -        --Binary , default 16 binaryTen     ) One     RETURNS BIGINT A  as - BEGIN -     DECLARE @result BIGINT the     DECLARE @ym VARCHAR( -)--Mask -     SET @ym = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' [email protected]#$%^&* ()-_=+[]{}\|;: ",. <>/?' -     DECLARE @i INT = LEN(@value)- 1 -     DECLARE @digit CHAR(1) +     SET @result =(CHARINDEX(SUBSTRING(@value,LEN(@value),1),@ym)-1) -     DECLARE @w INT +      while @i > 0 A     BEGIN at         SET @digit = SUBSTRING(@value,@i,1) -         SET @w =(CHARINDEX(@digit,@ym)-1)* POWER(@jz,(LEN(@value)- @i)) -         SET @result = @result + @w -         SET @i = @i - 1 -     END  -     RETURN @result in END

Test Case:

1 DECLARE @i BIGINT = 10000,@jz INT =  +2 DECLARE @str VARCHAR( -)=Dbo. BIGINTTOHEXSTR (@i,@jz)3 PRINT 'Digital' + CAST(@i  as VARCHAR( -))+ 'converted into' + CAST(@jz  as VARCHAR(2))+ 'The result of the binary is:'+ @str4 DECLARE @r BIGINT =Dbo. Hexstrtobigint (@str,@jz)5 PRINT CAST(@jz  as VARCHAR(2))+'the binary string' + @str + 'converted to numbers:' + CAST(@r  as VARCHAR( -))

Test results:

10000 the result of converting to 32 is: The 9OG32 string 9OG is converted to a number: 10000

Test environment

Windows 7 x64 + SQL Server Express R2

SQL binary conversion, support for binary conversions within 93