Hi All.
Today we will try to combine two MYSQL small integer values into one integer value.
Obviously, there is limit for the size of integer.
We will discuss only UNSIGNED integer column types (non negative values).
2 Solutions are suggested below:
Solution-1:
concatenate base-9 value of each number with '9' separator.
Solution-2:
concatenate the original values with padding (left) zeros.
[Note: if the values allowed to be NULL, solution-2 is not supported!].
# LIMITS #
First, lets learn about the range limits of each data-type:
Datatype | Type | UNSIGNED Range | Max value (base9) |
INT | A normal-size integer | 0 to 4294967295 | - |
SMALLINT | A small integer | 0 to 65535 | CONV(65535, 10, 9) = 108806 |
TINYINT | A very small integer | 0 to 255 | CONV(255, 10, 9) = 313 |
# SOLUTION-1 (Support NULL values) #
Combine and merge two small integers,
into 1 integer by converting each number to base-9 integer,
and then concatenate with the digit '9' as separator.
Two SMALLINTs cannot be saved inside INT value:
108806
9108806 > 4294967295.
But combining SMALLINT with TINYINT is possible (and aswell two TINYINT).
MYSQL CODE:
############ TABLE DEFINITION:
CREATE TABLE `table_test` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`sint1` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`tint2` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE);
############ SELECT:
SELECT T.*,
CONV(SUBSTRING_INDEX(combined,'9',1), 9,10) as _sint1,
CONV(SUBSTRING_INDEX(combined,'9',-1),9,10) as _tint2
FROM (
SELECT id, sint1, tint2,
CONCAT( IFNULL(CONV(sint1, 10, 9),''),
'9',
IFNULL(CONV(tint2, 10, 9),'')
) AS combined
FROM table_test
) AS T
And output will be:
id | sint1 | tint2 | >combined | >_sint1 | >_tint2 |
1 | NULL | NULL | 9 | NULL | NULL |
2 | 1234 | NULL | 16219 | 1234 | NULL |
3 | NULL | 255 | 9313 | NULL | 255 |
4 | 3123 | 38 | 4250942 | 3123 | 38 |
5 | 3410 | 243 | 46089300 | 3410 | 243 |
6 | 2685 | 207 | 36139250 | 2685 | 207 |
7 | 1327 | 224 | 17349268 | 1327 | 224 |
8 | 2047 | 102 | 27249123 | 2047 | 102 |
9 | 2953 | 189 | 40419230 | 2953 | 189 |
10 | 3686 | 117 | 50459140 | 3686 | 117 |
11 | 4455 | 18 | 6100920 | 4455 | 18 |
12 | 2482 | 66 | 3357973 | 2482 | 66 |
13 | 3072 | 74 | 4183982 | 3072 | 74 |
14 | 2036 | 42 | 2712946 | 2036 | 42 |
15 | 2047 | 139 | 27249164 | 2047 | 139 |
16 | 1573 | 236 | 21379282 | 1573 | 236 |
17 | 2464 | 173 | 33379212 | 2464 | 173 |
18 | 3656 | 155 | 50129182 | 3656 | 155 |
19 | 3305 | 120 | 44729143 | 3305 | 120 |
20 | 5894 | 121 | 80689144 | 5894 | 121 |
# SOLUTION-2 (NULLS not supported) #
Here we just merge the original values with LEFT-PADDING of zeros.
MYSQL CODE:
############ TABLE DEFINITION:
CREATE TABLE `table_test` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`sint1` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`tint2` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE);
############ SELECT:
SELECT T.*,
SUBSTR(combined,1,LENGTH(combined)-3)+0 AS _sint1,
RIGHT(combined,3)+0 AS _tint2
FROM (
SELECT id, sint1, tint2,
CONCAT(sint1, LPAD(tint2,3,0)) AS combined
FROM table_test
) AS T
And output will be:
id | sint1 | tint2 | >combined | >_sint1 | >_tint2 |
4 | 3123 | 38 | 3123038 | 3123 | 38 |
5 | 3410 | 243 | 3410243 | 3410 | 243 |
6 | 2685 | 207 | 2685207 | 2685 | 207 |
7 | 1327 | 224 | 1327224 | 1327 | 224 |
8 | 2047 | 102 | 2047102 | 2047 | 102 |
9 | 2953 | 189 | 2953189 | 2953 | 189 |
10 | 3686 | 117 | 3686117 | 3686 | 117 |
11 | 4455 | 18 | 4455018 | 4455 | 18 |
12 | 2482 | 66 | 2482066 | 2482 | 66 |
13 | 3072 | 74 | 3072074 | 3072 | 74 |
14 | 2036 | 42 | 2036042 | 2036 | 42 |
15 | 2047 | 139 | 2047139 | 2047 | 139 |
16 | 1573 | 236 | 1573236 | 1573 | 236 |
17 | 2464 | 173 | 2464173 | 2464 | 173 |
18 | 3656 | 155 | 3656155 | 3656 | 155 |
19 | 3305 | 120 | 3305120 | 3305 | 120 |
20 | 5894 | 121 | 5894121 | 5894 | 121 |
This solution can work with two SMALLINT,
if one of them limited up to 42949,
and it will be the left operand.