MySQL | join/combine 2 SMALLINT/TINYINT into 1 INT

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:
1088069108806 > 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.