CODEWITHSUNDEEP
mysqlsql
chongzixin
chongzixin

Reputation: 1981

MySQL Convert Numbers to letters

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?

Upvotes: 2

Views: 5214

Answers (5)

khelwood
khelwood

Reputation: 59222

I was just looking for something like this and I found a way to do it with the CONV function.

CONV(9+your_number, 10, 36)

This converts 1 to A, 2 to B etc.

The way it works is by adding 9 and then converting to base 36, in which 10 is A, 11 is B etc.

Upvotes: 4

user8124226
user8124226

Reputation: 104

Maybe can use CASE WHEN() END; and stored procedure

For example:

CREATE DEFINER = 'USERNAME'@'HOST' STORED PROCEDURE `ConvertNumberToAlphabetic`
BEGIN
SELECT 
(CASE
    WHEN (your_number = '1') THEN 'A'
    WHEN (your_number = '2') THEN 'B'
    WHEN (your_number = '3') THEN 'C'
    WHEN (your_number = '4') THEN 'D'
    WHEN (your_number = '5') THEN 'E'
    WHEN (your_number = '6') THEN 'F'
    WHEN (your_number = '7') THEN 'G'
    WHEN (your_number = '8') THEN 'H'
    WHEN (your_number = '9') THEN 'J'
    WHEN (your_number = '10') THEN 'K'
END) RedeemCode
FROM tblTransaction;
END

Upvotes: 0

David Jashi
David Jashi

Reputation: 4511

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))

Upvotes: 2

Ahsan Mahboob Shah
Ahsan Mahboob Shah

Reputation: 4029

SELECT SUBSTRING(MD5(transactionId) FROM 1 FOR 4) AS RedemptionCode

This creates a 4 character (easy to change, as you can see) string where the characters are from the MD5 command (and therefore in the range a-z and 0-9).

Upvotes: -1

Starx
Starx

Reputation: 79049

You can generate a hash with the value of transaction id.

Like:

SELECT MD5(transactionID) FROM `yourtable`;

There are several other types of similar functions you can use.

Upvotes: 0

Related Questions