MySQL Convert Numbers to Alphabets
User1258600’s Question:
I have an auto-increment transactionID type=MEDIUMINT(9) in my table. I want to also display a unique 4-character (which can grow over time, but 4 for now) alphabetical Redemption Code to my users. What is the best way to derive this alphabetical code from my transactionID, preferably straight from the SELECT statement?
Thank you. 🙂
That mostly depends on what alphabet you want to use.
You may use TO_BASE64 to convert it it to base64 encoded string or simply do something like:
select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(your_number, '0', 'A')
, '1', 'B')
, '2', 'C')
, '3', 'D')
, '4', 'E')
, '5', 'F')
, '6', 'G')
, '7', 'H')
, '8', 'I')
, '9', 'J')
if you want custom alphabet.
In case you want something shorter, you can go a slightly harder way:
You use 9-digit decimal (maximum 999999999), which translates to 8 hex digits (0x3B9AC9FF), i.e. 4 bytes. What you can do is divide your number in 4 binary octets, convert them to chars, construct new string and feed it to TO_BASE64():
select TO_BASE64(CONCAT(CHAR(FLOOR(your_number/(256*256*256))%256),CHAR(FLOOR(your_number/(256*256))%256),CHAR(FLOOR(your_number/256)%256),CHAR(your_number%256)))
Note, that TO_BASE64() function is available only in MySQL 5.6 on-wards.
Now, for those on older versions – we don’t want to implement base64 encoding with our bare hands, don’t we? So, lets go the easier way: we have 30 bits in those 9 decimal digits, which would be 30/6=5 characters, if we use 64 continuous character alphabet after CHAR(32), which is space, which we don’t want to use:
SELECT CONCAT(`enter code here`CHAR(FLOOR(your_number/(64*64*64*64))%64+33),CHAR(FLOOR(your_number/(64*64*64))%64+33),CHAR(FLOOR(your_number/(64*64))%64+33),CHAR(FLOOR(your_number/64)%64+64),CHAR(your_number%64+33))
