3

In our DB we use a primary key for everything that is a combination of the digits 0 through 9 and all the letters in the alphabet that are not vows. the letter Y is included. I created this table to organize it.

CREATE Table Base31
(ID varchar(1),
 NumberValue bigint)

GO

Insert into Base31 SELECT '0', '0'
Insert into Base31 SELECT '1', '1'
Insert into Base31 SELECT '2', '2'
Insert into Base31 SELECT '3', '3'
Insert into Base31 SELECT '4', '4'
Insert into Base31 SELECT '5', '5'
Insert into Base31 SELECT '6', '6'
Insert into Base31 SELECT '7', '7'
Insert into Base31 SELECT '8', '8'
Insert into Base31 SELECT '9', '9'
Insert into Base31 SELECT 'B', '10'
Insert into Base31 SELECT 'C', '11'
Insert into Base31 SELECT 'D', '12'
Insert into Base31 SELECT 'F', '13'
Insert into Base31 SELECT 'G', '14'
Insert into Base31 SELECT 'H', '15'
Insert into Base31 SELECT 'J', '16'
Insert into Base31 SELECT 'K', '17'
Insert into Base31 SELECT 'L', '18'
Insert into Base31 SELECT 'M', '19'
Insert into Base31 SELECT 'N', '20'
Insert into Base31 SELECT 'P', '21'
Insert into Base31 SELECT 'Q', '22'
Insert into Base31 SELECT 'R', '23'
Insert into Base31 SELECT 'S', '24'
Insert into Base31 SELECT 'T', '25'
Insert into Base31 SELECT 'V', '26'
Insert into Base31 SELECT 'W', '27'
Insert into Base31 SELECT 'X', '28'
Insert into Base31 SELECT 'Y', '29'
Insert into Base31 SELECT 'Z', '30'

I'm trying to convert these primary keys to numeric values but i keep running int a int limitation. I have changes everything to use a big int data type. How can I get the arithmetic to use the big int data type? 

GO
CREATE Proc Base_31 @v varchar(8)

as    

DECLARE @Ai bigint
DECLARE @Bi bigint
DECLARE @Ci bigint
DECLARE @Di bigint
DECLARE @Ei bigint
DECLARE @Fi bigint
DECLARE @Gi bigint
DECLARE @Hi bigint

SELECT @Ai = NumberValue                 from Base31 where      RIGHT(@v,1)    = ID
SELECT @Bi = (NumberValue * POWER(31,1)) from Base31 where LEFT(RIGHT(@v,2),1) = ID
SELECT @Ci = (NumberValue * POWER(31,2)) from Base31 where LEFT(RIGHT(@v,3),1) = ID
SELECT @Di = (NumberValue * POWER(31,3)) from Base31 where LEFT(RIGHT(@v,4),1) = ID
SELECT @Ei = (NumberValue * POWER(31,4)) from Base31 where LEFT(RIGHT(@v,5),1) = ID
SELECT @Fi = (NumberValue * POWER(31,5)) from Base31 where LEFT(RIGHT(@v,6),1) = ID
SELECT @Gi = (NumberValue * POWER(31,6)) from Base31 where LEFT(RIGHT(@v,7),1) = ID
SELECT @Hi = (NumberValue * POWER(31,7)) from Base31 where LEFT(      @v   ,1) = ID

SELECT @v       AS Original
     , Cast(@Ai AS bigint) 
     + Cast(@Bi AS bigint) 
     + Cast(@Ci AS bigint) 
     + Cast(@Di AS bigint) 
     + Cast(@Ei AS bigint) 
     + Cast(@Fi AS bigint) 
     + Cast(@Gi AS bigint)
     + Cast(@Hi AS bigint) 
     as [Base 31 converted]
GO
Joe
  • 55
  • 5
  • But there is no semantics to this PK definition, it is just a handle. It can be replaced by any arbitrary handle, such as a monotonically increasing int. – Pieter Geerkens Mar 07 '13 at 18:11

2 Answers2

2

The problem is that POWER function returns same type as it's first parameter. So you just have to explicitly cast first parameter to bigint.

This statement fails:

select power(31,7)

And this one works:

select power(cast(31 as bigint),7)
AdamL
  • 12,421
  • 5
  • 50
  • 74
2

There's a better way to do this. The methods below will work for arbitrary length numbers and arbitrary bases with only minor adjustments:

-- First convert a bigint to base-31 number string.
DECLARE @number bigint

SET @number = 123740194325432

DECLARE @Base31 table
(Digit varchar(1) NOT NULL,
 Value bigint NOT NULL PRIMARY KEY)

INSERT into @Base31 (Digit, Value) VALUES ('0', '0')
INSERT into @Base31 (Digit, Value) VALUES ('1', '1')
INSERT into @Base31 (Digit, Value) VALUES ('2', '2')
INSERT into @Base31 (Digit, Value) VALUES ('3', '3')
INSERT into @Base31 (Digit, Value) VALUES ('4', '4')
INSERT into @Base31 (Digit, Value) VALUES ('5', '5')
INSERT into @Base31 (Digit, Value) VALUES ('6', '6')
INSERT into @Base31 (Digit, Value) VALUES ('7', '7')
INSERT into @Base31 (Digit, Value) VALUES ('8', '8')
INSERT into @Base31 (Digit, Value) VALUES ('9', '9')
INSERT into @Base31 (Digit, Value) VALUES ('B', '10')
INSERT into @Base31 (Digit, Value) VALUES ('C', '11')
INSERT into @Base31 (Digit, Value) VALUES ('D', '12')
INSERT into @Base31 (Digit, Value) VALUES ('F', '13')
INSERT into @Base31 (Digit, Value) VALUES ('G', '14')
INSERT into @Base31 (Digit, Value) VALUES ('H', '15')
INSERT into @Base31 (Digit, Value) VALUES ('J', '16')
INSERT into @Base31 (Digit, Value) VALUES ('K', '17')
INSERT into @Base31 (Digit, Value) VALUES ('L', '18')
INSERT into @Base31 (Digit, Value) VALUES ('M', '19')
INSERT into @Base31 (Digit, Value) VALUES ('N', '20')
INSERT into @Base31 (Digit, Value) VALUES ('P', '21')
INSERT into @Base31 (Digit, Value) VALUES ('Q', '22')
INSERT into @Base31 (Digit, Value) VALUES ('R', '23')
INSERT into @Base31 (Digit, Value) VALUES ('S', '24')
INSERT into @Base31 (Digit, Value) VALUES ('T', '25')
INSERT into @Base31 (Digit, Value) VALUES ('V', '26')
INSERT into @Base31 (Digit, Value) VALUES ('W', '27')
INSERT into @Base31 (Digit, Value) VALUES ('X', '28')
INSERT into @Base31 (Digit, Value) VALUES ('Y', '29')
INSERT into @Base31 (Digit, Value) VALUES ('Z', '30')

DECLARE @base bigint
DECLARE @power bigint
DECLARE @divisor bigint
DECLARE @output varchar(50)
DECLARE @digit varchar(1)
DECLARE @digitValue bigint

SET @output = ''
SET @base = 31

IF @number < 0
BEGIN
    SET @number = ABS(@number)
    SET @output = '-'
END

IF NOT @number = 0
BEGIN   
    /* Due to the fact that the LOG10 function in SQL yields a float which limits the 
    available precision of the logarithm, numbers above 147209586032873 may have some 
    artifacts, i.e. LOG10(147209586032874) = LOG10(147209586032873). In actual practice, 
    I've found that numbers above 31^10 - 5 may manifest a leading zero when they are 
    near a power of 31, i.e. numbers in the ranges 31^10 - 4 to 31^10 - 1 and 
    31^11 - 158 to 31^11 - 1. */

    SET @power = FLOOR(LOG10(@number) / LOG10(@base))
END

WHILE @power >= 0 AND @number > 0
BEGIN
    SET @divisor = POWER(@base, @power)
    SET @digitValue = @number / @divisor
    SET @number = @number % @divisor

    SELECT @digit = Digit
    FROM @Base31
    WHERE Value = @digitValue

    SELECT @output = @output + @digit
    SET @power = @power - 1
END

SET @output = @output + COALESCE(REPLICATE('0', @power + 1), '0')

SELECT @output

-- Now convert the base-31 number string back to a bigint and see if we're right.
DECLARE @position bigint

SET @position = LEN(@output)
SET @number = 0

WHILE @position > 0
BEGIN
    SET @digit = SUBSTRING(@output, @position, 1)

    SELECT @digitValue = Value
    FROM @base31
    WHERE Digit = @digit

    SET @power = LEN(@output) - @position
    SET @number = @number + (@digitValue * POWER(@base, @power))
    SET @position = @position - 1
END

SELECT @number
JamieSee
  • 12,696
  • 2
  • 31
  • 47