Ryan Ryan - 5 months ago 7
SQL Question

How can one determine if a field is numeric or a string in MySQL?

I've tried both

CEIL
and
CAST
methods suggested elsewhere. Neither of them work.

SELECT
CASE WHEN CEIL('foo') = 'foo' THEN 'Str' ELSE 'Num' END AS ceil_test_1,
CASE WHEN CEIL(123) = 123 THEN 'Str' ELSE 'Num' END AS ceil_test_2,
CASE WHEN CEIL('123') = 123 THEN 'Str' ELSE 'Num' END AS ceil_test_3,
CASE WHEN CAST('bar' AS UNSIGNED) = 'bar' THEN 'Str' ELSE 'Num' END AS cast_test_1,
CASE WHEN CAST(123 AS UNSIGNED) = 123 THEN 'Str' ELSE 'Num' END AS cast_test_2,
CASE WHEN CAST('123' AS UNSIGNED) = 123 THEN 'Str' ELSE 'Num' END AS cast_test_3
;


Expected Results are obvious:
foo
is a string.
123
is a number.

Answer

You can use REGEXP for that purpose.

SET @str := 'asdf', @str2 := '1d25';

SELECT CASE WHEN @str REGEXP '^[A-Z a-z]+$' THEN 'YES' ELSE 'NO' END AS isString;
SELECT CASE WHEN @str2 REGEXP '^[0-9]+$' THEN 'YES' ELSE 'NO' END AS isNumber;

Note: (In regex, ^ means begin, and $ means end)