SQL ISNUMERIC Function

SQL ISNUMERIC Function

The SQL ISNUMERIC function is used to determine whether an expression is a valid numeric type. It returns 1 if the expression can be converted to a number; otherwise, it returns 0.

This function is commonly used to validate data entries, filter numeric values, and perform conditional logic in SQL queries.

---

Syntax of SQL ISNUMERIC Function

The basic syntax of the ISNUMERIC function is as follows:

SELECT ISNUMERIC(expression);

Parameters:

• expression: The value or column to check for numeric validity.

The function returns:

• 1 – If the value is numeric.
• 0 – If the value is not numeric.

---

Step-by-Step Examples Using SQL ISNUMERIC

1. Checking if Values are Numeric

Let’s test various inputs using the ISNUMERIC function:

SELECT ISNUMERIC('123') AS Result1, 
       ISNUMERIC('Arjun') AS Result2, 
       ISNUMERIC('45.67') AS Result3, 
       ISNUMERIC('10A') AS Result4;

Explanation:

• '123' is a valid number, so ISNUMERIC returns 1.
• 'Arjun' is not a number, so ISNUMERIC returns 0.
• '45.67' is a valid decimal number, so ISNUMERIC returns 1.
• '10A' contains a non-numeric character, so ISNUMERIC returns 0.

---

2. Using ISNUMERIC in a WHERE Clause

We can use ISNUMERIC to filter only numeric values in a table. Let’s create a sample customers table:

CREATE TABLE customers (
    id INT PRIMARY KEY IDENTITY(1, 1),
    name VARCHAR(50),
    contact_info VARCHAR(50)
);

Insert sample data:

INSERT INTO customers (name, contact_info)
VALUES 
('Arjun', '9876543210'),
('Priya', 'email@example.com'),
('Ram', '4567891234'),
('Sara', 'contact@domain');

Now, let’s retrieve only those customers whose contact information is numeric:

SELECT name, contact_info 
FROM customers 
WHERE ISNUMERIC(contact_info) = 1;

Explanation:

• The ISNUMERIC(contact_info) = 1 condition filters out non-numeric contact information.
• The result will include only 'Arjun' and 'Ram', as their contact info contains numeric values.

---

Conclusion

In this tutorial, how ISNUMERIC function in SQL is used for:

1. Checking if a value can be converted into a numeric data type.
2. Filtering numeric data within a table.
3. Ensuring data validation in queries.