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

Question

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.

Possible duplicate of [Cast from VARCHAR to INT - MySQL](http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) — dfundako, Jul 14 '16 at 14:46

score 2 · Accepted Answer · answered Jul 14 '16 at 14:57

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

score 0 · Answer 2 · answered Jul 14 '16 at 14:53

0

SELECT CAST(CHAR1 AS UNSIGNED) FROM TABLE1

answered Jul 14 '16 at 14:53

olegsv

1,422
1
14
21

From what I understand, the OP would like to convert any value to an integer, for instance values that do not represent or even contain any digit. – julienc Jul 14 '16 at 15:02
That's correct, the column value could be any alphanumeric character – ps0604 Jul 14 '16 at 15:09

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

2 Answers2