SQL Server ISNUMERIC() Function

SQL Server ISNUMERIC() Function

The SQL Server ISNUMERIC() function returns an integer that indicates whether an expression is a valid numeric type. This function is useful for validating numeric data in SQL queries.

Syntax

SELECT ISNUMERIC(expression);

The ISNUMERIC() function takes a single argument:

  • expression: The value to be evaluated. It can be a column, variable, or a constant.

Example SQL Server ISNUMERIC() Function Queries

Let's look at some examples of SQL Server ISNUMERIC() function queries:

1. Basic ISNUMERIC() Example

SELECT ISNUMERIC('123') AS result;

This query checks if the string '123' is numeric. The result will be:

result
------
1

2. Checking a Non-Numeric String

SELECT ISNUMERIC('ABC') AS result;

This query checks if the string 'ABC' is numeric. The result will be:

result
------
0

3. ISNUMERIC() with Column Values

SELECT column_name, ISNUMERIC(column_name) AS is_numeric
FROM table_name;

This query checks if each value in column_name is numeric. The result will show the original column_name and the corresponding is_numeric status.

4. ISNUMERIC() with a Variable

DECLARE @value VARCHAR(50) = '456';
SELECT ISNUMERIC(@value) AS result;

This query uses a variable to store a string value and checks if it is numeric. The result will be:

result
------
1

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the ISNUMERIC() function.

Step 1: Creating a Table

This step involves creating a new table named items to store some sample data with item codes.

CREATE TABLE items (
    id INT PRIMARY KEY,
    item_code VARCHAR(50)
);

In this example, we create a table named items with columns for id and item_code.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the items table.

INSERT INTO items (id, item_code) VALUES (1, '123');
INSERT INTO items (id, item_code) VALUES (2, '456');
INSERT INTO items (id, item_code) VALUES (3, 'ABC');

Here, we insert data into the items table.

Step 3: Using the ISNUMERIC() Function

This step involves using the ISNUMERIC() function to check if each item_code is numeric.

SELECT id, item_code, ISNUMERIC(item_code) AS is_numeric
FROM items;

This query retrieves the id, item_code, and the numeric status of the item_code column for each row in the items table. The result will be:

id  item_code  is_numeric
--- ---------- -----------
1   123        1
2   456        1
3   ABC        0

Conclusion

The SQL Server ISNUMERIC() function is a powerful tool for validating whether an expression is numeric. Understanding how to use the ISNUMERIC() function and its syntax is essential for effective data validation and processing in SQL Server.