1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
|
-- This function return the decimal value for a value in a certain base.
-- It returns -1 if the string of the value contains any character not compatible with the base
-- or if the base is lower than 2 or bigger than 36
CREATE FUNCTION dbo.fn_BaseToDec(@value AS varchar(64), @base AS int)
RETURNS bigint
AS
BEGIN
-- declare @base int
-- declare @value varchar(50)
declare @r AS bigint
declare @alldigits AS varchar(36)
declare @pos as int
declare @letterValue as int
-- Check the base. Should not be lower than 2 and should not be bigger than 36
if @base < 2 or @base > 36
BEGIN
return -1
END
set @r = 0
-- Parameters/Debug mode
-- set @value = '20'
-- set @base = 36
-- Get the list of existing characters for a specific base
SET @alldigits = substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',0,@base + 1)
-- Get the lenght of the string we need to convert
set @pos = len(@value)
-- Parse all the string
WHILE @pos > 0
BEGIN
-- Get the letter value
-- Patindex return 0 if the string is not found
-- return 1 if it is the first number
-- so we first have to test if patindex - 1 >= 0 otherwise generate an error
set @letterValue = PATINDEX('%' + substring(@value, @pos, 1) + '%',@alldigits) - 1
-- Test if the letter is in the base list of characters. Else return -1.
if @letterValue < 0
begin
return -1
end
--print 'pos : ' + convert(varchar,@pos)
-- Add the value to the return value
SET @r = @r + @letterValue * power(@base, len(@value) - @pos)
--print 'r : ' + convert(varchar,@r)
-- Go to the next character
SET @pos = @pos - 1
END
return @r;
END |
Partager