Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to only select numeric data from mysql?

Tags:

mysql

numeric

Does mysql have a function like is_num() which can allow me determine the data is numeric or not?

like image 530
user295515 Avatar asked May 03 '10 07:05

user295515


People also ask

How do I select only numeric values in SQL?

In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column. We can alternatively run a separate query to return all values that contain numeric data.

How do I select only the value of an integer in MySQL?

Syntax to check if the value is an integer. select yourColumnName from yourTableName where yourColumnName REGEXP '^-?[0-9]+$'; The query wherein we have used regular expression. This will output only the integer value.

How do I find the numeric values of a column in MySQL?

One way to check if a string is numeric is by writing a regular expression using the REGEXP operator. The above regular expression will match the numerical value between 0 to 9 from the start of the value until the end.

What is %s and %D in MySQL?

12 years, 10 months ago. it's for php to know how to handle the parameters, %d – the argument is treated as an integer, and presented as a (signed) decimal number. %s – the argument is treated as and presented as a string. in your examples, $slug is a string and $this->id is an integer.


1 Answers

You may want to create a user defined function that matches the value against a regular expression:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint 
   RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'; 

Source: MySQL Forums :: Microsoft SQL Server :: IsNumeric() clause in MySQL?


Truthy Tests:

mysql> SELECT ISNUMERIC('1');
+----------------+
| ISNUMERIC('1') |
+----------------+
|              1 |
+----------------+
1 row in set (0.01 sec)

mysql> SELECT ISNUMERIC(25);
+---------------+
| ISNUMERIC(25) |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('-100');
+-----------------+
| ISNUMERIC(-100) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.03 sec)

mysql> SELECT ISNUMERIC('1.5');
+------------------+
| ISNUMERIC('1.5') |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('-1.5');
+-------------------+
| ISNUMERIC('-1.5') |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.02 sec)

Falsy Tests:

mysql> SELECT ISNUMERIC('a');
+----------------+
| ISNUMERIC('a') |
+----------------+
|              0 |
+----------------+
1 row in set (0.02 sec)

mysql> SELECT ISNUMERIC('a1');
+-----------------+
| ISNUMERIC('a1') |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('10a');
+------------------+
| ISNUMERIC('10a') |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('0.a');
+------------------+
| ISNUMERIC('0.a') |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('a.0');
+------------------+
| ISNUMERIC('a.0') |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
like image 162
Daniel Vassallo Avatar answered Sep 27 '22 22:09

Daniel Vassallo