Isnumeric in MySQL

1. Use Regular Expressions to Check if Values Are Numeric in MySQL
2. Use the concat Function to Check if Values Are Numeric in MySQL

This tutorial aims to check if the values in a table have numeric characters in MySQL.

Many-a-times, businesses and organizations need to check if a particular value in the rows of a particular column is numeric or not. It is common to ensure data consistency and data integrity in the database.

For example, a product-based company might require to use this function or method to ensure that all the users have or do not have a numeric character in their usernames or some other related field.

Data analysts can use regular expressions and apply a simple clause for the field under consideration to get this done.

Use Regular Expressions to Check if Values Are Numeric in MySQL

Let us understand how this method works and how to do this in a MySQL database.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details_table, along with a few rows.

-- create the table student_details_table
CREATE TABLE student_details_table(
  stu_id int,
  stu_firstName varchar(255),
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id, stu_firstName)
);
-- insert rows to the table student_details_table
INSERT INTO student_details_table(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Here as we can see, we have set the stu_id and stu_firstName as the primary key for our table student_details_table. It would ensure that the values in this column cannot be duplicated or NULL.

It should also be noted that a primary key cannot possess Null values in its column. We will certainly get an error if we push or insert Null values in this column.

Sometimes, it becomes necessary for organizations to get rid of this key to insert multiple similar values or null values.

The query above creates a table with rows of the student’s first and last name. To view the entries in the data, we use the following code.

SELECT * FROM student_details_table;

The query would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now let us use the DESCRIBE statement to understand our table in greater depth. This operation can be performed with the help of the following query.

DESCRIBE name_of_the_table;

In our case, we need to write the following query to understand the details of the table student_details_table.

DESCRIBE student_details_table;

This statement would help us fetch the table’s intricate details like the data type associated with each column, different columns and their names, the keys associated with each column, and any extra information relevant to the table.

The query would give us the following output.

Field				Type			Null	Key		Default		Extra
stu_id				int				NO		PRI
stu_firstName		varchar(255)	NO		PRI
stu_lastName		varchar(255)	YES

Now, let us check if the values in table student_details_table with the stu_id field have numeric characters.

To do this, we need to use regular expressions and match the value of the rows with an expression. The expression can be stated as: '^[0-9]+$'.

It can help us check if there exist any numeric characters in the table. We can get this operation done with the help of the following query.

SELECT * FROM student_details_table WHERE stu_id REGEXP '^[0-9]+$';

As shown from the table and the query, we will fetch all the numeric records in the stu_id column. The output of the query can be illustrated as follows.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

We get the entire table as the output as each one of these entries has a number associated with the stu_id column.

To dive deeper into this question, let us filter students from our table such that we only fetch details of the students with numbers in the range of 0 to 5.

The students with stu_id greater than 5 should be eliminated. We can do this using the following query.

SELECT * FROM student_details_table WHERE stu_id REGEXP '^[0-5]+$';

The query would help us fetch the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah

As shown in the output above, students with stu_id greater than 5 have been filtered out. Thus, with the help of regular expressions, we can check if a column of a particular table has numeric values.

Use the concat Function to Check if Values Are Numeric in MySQL

Another method to get this done would be using the concat function. The operation can be performed using the following query.

SELECT * FROM student_details_table WHERE concat('',stu_id * 1) = stu_id;

It would ensure that only those entries from the stu_id column are fetched in numeric form.

Therefore, with the help of regular expressions and the concat function, we can efficiently check or fetch data with numeric entries of a table in MySQL.

Some other related topics that can help learn the concept better are below.

• Regular Expressions in MySQL.
• Concat function in MySQL.