Detect if value is number in MySQL

Question

Is there a way to detect if a value is a number in a MySQL query  Such as  SELECT    FROM myTable  WHERE isANumber col1    true

User · Answer

Still missing this simple version   SELECT   FROM myTable WHERE  col1    0    col1     addition should be faster as multiplication   Or slowest version for further playing   SELECT     CASE WHEN  col1    0    col1  THEN 1 ELSE 0 END AS  IS NUMERIC   FROM  myTable  HAVING  IS NUMERIC    1

User · Answer

This should work in most cases   SELECT   FROM myTable WHERE concat    col1   1    col1   It doesn t work for non-standard numbers like   1e4 1 2e5 123    trailing decimal

User · Answer

You can use Regular Expression too    it would be like   SELECT   FROM myTable WHERE col1 REGEXP    0-9        Reference  http   dev mysql com doc refman 5 1 en regexp html

User · Answer

This answer is similar to Dmitry  but it will allow for decimals as well as positive and negative numbers   select   from table where col1 REGEXP      digit

User · Answer

I have found that this works quite well  if col1 col1  1  number  col1  AS myInfo

User · Answer

You can use regular expression for the mor detail https   dev mysql com doc refman 8 0 en regexp html I used this          0-9      This is allows handle to the decimal and float number SELECT       FROM     mytable WHERE     myTextField REGEXP  quot          0-9     quot

User · Answer

If your data is  test    test0    test1111    111test    111   To select all records where the data is a simple int   SELECT    FROM myTable  WHERE col1 REGEXP    0-9        Result   111    In regex    means begin  and   means end   To select all records where an integer or decimal number exists   SELECT    FROM myTable  WHERE col1 REGEXP    0-9       0-9      - for 123 12   Result   111   same as last example   Finally  to select all records where number exists  use this   SELECT    FROM myTable  WHERE col1 REGEXP   0-9       Result   test0  and  test1111  and  111test  and  111

User · Answer

I recommend  if your search is simple   you can use    column 1   column     operator interesting    is work and faster than on fields varchar char      SELECT   FROM myTable WHERE column 1   column    ABC 1   gt  0  NOT EQU   ABC    AB15 A   gt  15  NOT EQU   AB15    15AB   gt  15  NOT EQU   15AB    15   gt  15  EQUALS TRUE   15

User · Answer

SELECT   FROM myTable WHERE col1 REGEXP     -   0-9    0-9      0-9      0-9   0-9   e  -   0-9         Will also match signed decimals  like -1 2   0 2  6   2e9  1 2e-10    Test   drop table if exists myTable  create table myTable  col1 varchar 50    insert into myTable  col1     values   00 00      1      123     - 23e4     12 e-5     3 5e 6     a     e6      e0     select    col1    col1   0 as casted    col1 REGEXP     -   0-9    0-9      0-9      0-9   0-9   e  -   0-9       as isNumeric from myTable    Result   col1      casted   isNumeric ------- --------- ---------- 00 00          0           1  1             1           1  123       0 123           1 - 23e4     -2300           1 12 e-5   0 00012           1 3 5e 6   3500000           1 a              0           0 e6             0           0  e0            0           0   Demo

User · Answer

SELECT   FROM myTable WHERE sign  col1   0   ofcourse sign 0  is zero  but then you could restrict you query to     SELECT   FROM myTable WHERE sign  col1   0 or col1 0      UPDATE  This is not 100  reliable  because  1abc  would return sign of   1  but  ab1c  would return zero    so this could only work for text that does not begins with numbers

User · Answer

use a UDF  user defined function    CREATE FUNCTION isnumber inputValue VARCHAR 50     RETURNS INT   BEGIN     IF  inputValue REGEXP     0-9           THEN       RETURN 1      ELSE       RETURN 0      END IF    END    Then when you query   select isnumber  383XXXX      --returns 0  select isnumber  38333434      --returns 1  select isnumber mycol  mycol1  col2  colx from tablex  -- will return 1s and 0s for column mycol1  --you can enhance the function to take decimals  scientific notation   etc     The advantage of using a UDF is that you can use it on the left or right side of your  where clause  comparison  this greatly simplifies your SQL before being sent to the database    SELECT   from tablex where isnumber columnX    isnumber  UnkownUserInput      hope this helps

User · Answer

Try Dividing  1  select if value 1 gt 0 or value 0  its a number    its not a number   from table

User · Answer

Returns numeric rows  I found the solution with following query and works for me   SELECT   FROM myTable WHERE col1  gt  0    This query return rows having only greater than zero number column that col1  Returns non numeric rows  if you want to check column not numeric try this one with the trick   col1  gt  0    SELECT   FROM myTable WHERE  col1  gt  0

User · Answer

you can do using CAST     SELECT   from tbl where col1   concat cast col1 as decimal

User · Answer

Another alternative that seems faster than REGEXP on my computer is  SELECT   FROM myTable WHERE col1 0    col1    This will select all rows where col1 starts with a numeric value

[mysql] Detect if value is number in MySQL

Examples related to mysql

Examples related to sql

Examples related to where-clause