ps0604 ps0604 - 4 months ago 7
MySQL Question

How to get a numeric signature of a CHAR column in MySql

Given a CHAR or VARCHAR column in MySql, what I need is to convert it to a number so it can be summed up. For example, if the column name is CHAR1, the SELECT would be:

SELECT SUM(ConvertToNumber(CHAR1)) from TABLE1


The function
ConvertToNumber
just needs to convert to a number, it doesn't matter which number as long as it always converts to the same.

Is this feasible with native or user defined functions?

UPDATE: To be clear, the values of CHAR1 can be any string of alphanumeric characters.

Answer

What you can do is convert the column to the hexadecimal format, and then convert this result into base 10 to get an integer.

SELECT SUM(CONV(HEX(CHAR1), 16, 10)) FROM TABLE1;

For instance:

INSERT INTO TABLE1 (CHAR1) VALUES ("foo"), ("bar"), ("baz");

/* "int" value for each entry */
SELECT CONV(HEX(CHAR1), 16, 10) FROM TABLE1;

6713199
6447474
6447482

/* sum of the int values */
SELECT SUM(CONV(HEX(CHAR1), 16, 10)) FROM TABLE1

19608155
Comments