The short question; I have some account numbers that I need to generate an integer from. I could use the binary value for it, but it seems a bit long. Is there an algorithm I could use to convert them or will those numbers be just as long as the binary?

example of codes:

ACCO/COC/3263

ACCO/PDW/531

ACCO/CUP/558

My plan was to convert ACCO/CUP/ to a value, multiply it by 100000 and add the number on the end i.e.

The last code might look like this; 3462354600558

The full problem stands that when my data warehouse gets populated from the live data, I need some of the IDs to be converted to Integers so the SQL cube is more efficient and the data isn't so huge (30 million rows with those codes in takes up a bit more space than if it were Ints!). Ideally I need the IDs to stay the same every time I refresh the data, hence the idea of using an algorithm.

Any help would be much appreciated.

Regards,

Kinton

Then you have to find out a way to ensure each new accountnumber gets added into that table before you use it.