20

Good Morning,

I was looking for a way to combine two integers to create a unique number, I have two tables that I need to combine into a third table with unique numbers,

These are my tables:

Table A 
SchoolID    ClassId
107 56644231
107 56644532
107 320110212

Table B 
SchoolID    ClassId
108 566442310
108 56644532
108 50110212

I need to export these fields to a third table combining class ID and school ID into one single field called classID. I need to be able to combine these numbers together and then be able to uncombine them to get schoolid and classid separate for update purposes. I was thinking of concatenating the strings 'schoolid + '00' + 'classid' since I know that schoolid will always be a 3 digit number but I am looking for some other way perhaps mathematical where I don't have to use string casts.

Is there a mathematical way to do this? Or is casting to string the best way to do this?

I am using C# to code the solution.

Thanks,

Etienne de Martel
  • 34,692
  • 8
  • 91
  • 111
jangeador
  • 594
  • 1
  • 6
  • 17
  • 4
    Be careful that you do not create a new number greater than the max capacity of the storage type. – Inisheer Nov 22 '10 at 17:56

7 Answers7

22

Similar to Magnus Hoff, but I would recommend using a binary friendly approach instead of a base 10 approach.

combinedid = (classid << 8) + schoolid;

And then, later:

classid = combinedid >> 8;
schoolid = combinedid & 0xFF;

I think this is a little more straight forward from a programming standpoint (making it clear that your school ID is 1 byte (0-255), the class ID is 3 bytes).

You could also easily do this with a bigint (Long / Int64), making two int32's a single int64 safely:

combinedid = ((long)classid << 32) + schoolid;
userx
  • 3,769
  • 1
  • 23
  • 33
  • I like your answer a lot. Thanks. – jangeador Nov 23 '10 at 16:49
  • 2
    if classid is an int32 as mentioned, the code is not correct (classid << 32) will equal classid as it wraps around. correct would be: UInt64 combined = classid; combined <<= 32; // now it does not wrap around combined += schoolid; – citykid Oct 22 '12 at 23:43
  • @thomas - Technically you're right. I've updated it to cast classid to long before the bit shift. – userx Oct 23 '12 at 17:26
  • This is a really cool approach, but what about ambiguous situations like `(0 << 8) + 256` and `(1 << 8) + 0`? (Both `256`) Or, a less-contrived example: `(100 << 8) + 100` and `(99 << 8) + 356` (Both 25700). Doesn't this lead to an ambiguous encoding? – rinogo Sep 16 '22 at 15:03
  • @rinogo "making it clear that your school ID is 1 byte (0-255), the class ID is 3 bytes" – userx Sep 18 '22 at 06:18
  • @userx: The only property that OP mentioned was "schoolid will always be a 3 digit number", so I'm not sure this is technically answering the question. However, with your modified prerequisites, this approach works! – rinogo Sep 19 '22 at 16:42
9
combinedid = classid*1000 + schoolid

And then, later:

classid = combinedid / 1000 // Integer division
schoolid = combinedid % 1000
Magnus Hoff
  • 21,529
  • 9
  • 63
  • 82
6

I would combine the ID like this:

ID = ClassID * 1000 + SchoolID

You can then get the SchoolID like this:

SchoolID = ID % 1000

and you can get the ClassID like this:

ClassID = ID / 1000
Pieter van Ginkel
  • 29,160
  • 8
  • 71
  • 111
3

Use ((a + b)(a + b + 1) * 0.5) + b Reference http://en.wikipedia.org/wiki/Pairing_function

VenVig
  • 645
  • 1
  • 10
  • 14
2

This SO thread has detailed various other mathematical approaches, some are just better. But for your case I suspect if those are hardly good ideas. I would say you should save the two ids in a 3rd table which has its own unique id (primary key) column.

Community
  • 1
  • 1
nawfal
  • 70,104
  • 56
  • 326
  • 368
2

You indicate that you are working with a table. This leads me to believe that you are working within a database.

So I have to ask, why not store them in separate columns and make them simple foreign keys? Why create the ambiguity of trying to concatenate or transform the numbers with a math equation?

If you use another table, you can use an auto increment field and the combination of the three fields (in the order you designate) will give you a unique ID. 

|-------------------
| My_combine_table
|-------------------
| id   | auto_inc 
|-------------------
| SchoolID | ...
| SchoolID2 | ...
Frank V
  • 25,141
  • 34
  • 106
  • 144
  • 1
    I am exporting to a third party system which I have no control of. The local system is setup so that each school gets its own database, so I cannot control the classid's to be unique across all databases. The third party system does not have a place for schoolId, only ClassID – jangeador Nov 22 '10 at 17:59
2

If SchoolID is always a 3 digit number multiply ClassId by 1000 then add SchoolID.

Your number might "overflow" though., and given the ones you have if they are 32-bit they will.

CashCow
  • 30,981
  • 5
  • 61
  • 92
  • The system expect 32 bit integers so this may be a big problem. I hadn't thought of this. Thanks for pointing it out. – jangeador Nov 22 '10 at 18:01