When working with SQL Server, it is often necessary to determine whether a given string is numeric or not. SQL Server provides a default function called ISNUMERIC for this purpose. However, it is important to note that ISNUMERIC returns 1 for non-numerical values as well, which can lead to unexpected results.
To overcome this limitation, you can create a user-defined function that accurately determines if a string is numeric or not. Let’s take a look at an example:
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
In the above function, we use a regular expression to check if the string contains at least one character that is not a digit. By negating this condition with “NOT REGEXP”, we can determine if the string consists of only digits.
Let’s test this function with some examples:
SELECT '999' AS TestValue, udf_IsNumeric('999') AS NumericTest;
SELECT 'abc' AS TestValue, udf_IsNumeric('abc') AS NumericTest;
SELECT '9+9' AS TestValue, udf_IsNumeric('9+9') AS NumericTest;
SELECT '$9.9' AS TestValue, udf_IsNumeric('$9.9') AS NumericTest;
SELECT 'SQLAuthority' AS TestValue, udf_IsNumeric('SQLAuthority') AS NumericTest;
The result of the above queries will be:
TestValue | NumericTest |
---|---|
999 | 1 |
abc | 0 |
9+9 | 0 |
$9.9 | 0 |
SQLAuthority | 0 |
As you can see, the function accurately determines whether a string is numeric or not, providing more reliable results compared to the default ISNUMERIC function in SQL Server.
Regular expressions can be a powerful tool in SQL Server and can be used in various scenarios. If you want to learn more about regular expressions in MySQL, you can find additional examples here.