MySQL – UDF – Validate Integer Function

There is a default function in SQL Server called ISNUMERIC to determine if the string is numeric. In SQL Server ISNUMERIC returns 1 for non numerials also. Ex ISNUMERIC (‘,’) returns 1. To avoid this you can write a user defined function as shown over here. In MySQL, there is no default function like ISNUMERIC as in SQL Server. You need to create a similar user defined function as shown below. It uses a regular expression to validate integer values.

DELIMITER $$
CREATE FUNCTION 'udf_IsNumeric'(number_in VARCHAR(100)) RETURNS BIT
BEGIN
DECLARE Ret BIT;
IF number_in NOT regexp '[^0123456789]' THEN
SET Ret:= 1;
ELSE
SET Ret:= 0;
END IF;
RETURN Ret;
END

The above function uses Regular expression. The expression regexp ‘[^0123456789]’ will find out if the string has at least one character which is not a digit. The expression not regexp ‘[^0123456789]’ will just negate that condition so that you get the string which has all characters as a digit.

You can test this using the following example.

SELECT '999' TestValue,udf_IsNumeric('999') NumericTest;
SELECT 'abc' TestValue,udf_IsNumeric('abc') NumericTest;
SELECT '9+9' TestValue,udf_IsNumeric('9+9') NumericTest;
SELECT '$9.9' TestValue,udf_IsNumeric('$9.9') NumericTest;
SELECT 'SQLAuthority' TestValue,udf_IsNumeric('SQLAuthority') NumericTest;

The result is

TestValue NumericTest
 ——— ———–
 999 1

TestValue NumericTest
 ——— ———–
 abc 0

TestValue NumericTest
 ——— ———–
 9+9 0

TestValue NumericTest
 ——— ———–
 $9.9 0

TestValue NumericTest
 ———— ———–
 SQLAuthority 0

You can find more examples about Regular expressions in MySQL over here.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.