50% OFF!!!

Sunday, October 17, 2021

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:
DatatypeTypeUNSIGNED RangeMax value (base9)
INTA normal-size integer0 to 4294967295-
SMALLINTA small integer0 to 65535CONV(65535, 10, 9) = 108806
TINYINTA very small integer0 to 255CONV(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:
idsint1tint2>combined>_sint1>_tint2
1NULLNULL9NULLNULL
21234NULL162191234NULL
3NULL2559313NULL255
43123384250942312338
53410243460893003410243
62685207361392502685207
71327224173492681327224
82047102272491232047102
92953189404192302953189
103686117504591403686117
114455186100920445518
122482663357973248266
133072744183982307274
142036422712946203642
152047139272491642047139
161573236213792821573236
172464173333792122464173
183656155501291823656155
193305120447291433305120
205894121806891445894121

# 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:
idsint1tint2>combined>_sint1>_tint2
43123383123038312338
5341024334102433410243
6268520726852072685207
7132722413272241327224
8204710220471022047102
9295318929531892953189
10368611736861173686117
114455184455018445518
122482662482066248266
133072743072074307274
142036422036042203642
15204713920471392047139
16157323615732361573236
17246417324641732464173
18365615536561553656155
19330512033051203305120
20589412158941215894121

This solution can work with two SMALLINT,
if one of them limited up to 42949,
and it will be the left operand.


No comments:

Post a Comment