Wykryj, czy wartość jest liczbą w MySQL

Możesz również użyć wyrażenia regularnego... byłoby tak:

SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$';

Odniesienie: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Jeśli Twoje dane to "test", "test0", "test1111", "111test", " 111 "

Aby wybrać wszystkie rekordy, w których dane są prostą int:

SELECT * 
FROM myTable 
WHERE col1 REGEXP '^[0-9]+$';

Result: '111'

(w regex, ^ oznacza początek, a $ oznacza koniec)

Aby wybrać wszystkie rekordy, w których istnieje liczba całkowita lub dziesiętna:

SELECT * 
FROM myTable 
WHERE col1 REGEXP '^[0-9]+\\.?[0-9]*$'; - for 123.12

Wynik: '111 '(taki sam jak w poprzednim przykładzie)

Na koniec, aby wybrać wszystkie rekordy, w których istnieje liczba, użyj tego:

SELECT * 
FROM myTable 
WHERE col1 REGEXP '[0-9]+';

Wynik: 'test0' i 'test1111' oraz '111test' and " 111 "

SELECT * FROM myTable
WHERE col1 REGEXP '^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$'

Dopasuje również podpisane liczby dziesiętne (jak -1.2, +0.2, 6., 2e9, 1.2 e-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;

Wynik:

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

Zwraca wiersze liczbowe

Znalazłem rozwiązanie z następującym zapytaniem i działa dla mnie:

SELECT * FROM myTable WHERE col1 > 0;

To zapytanie zwraca wiersze mające tylko większą niż zero kolumnę liczbową, która col1

Zwraca nieliczbowe wiersze

Jeśli chcesz sprawdzić kolumnę, a nie numeryczną, spróbuj użyć tej sztuczki (!col1 > 0):

SELECT * FROM myTable WHERE !col1 > 0;

Ta odpowiedź jest podobna do Dmitrija, ale pozwoli na liczby dziesiętne oraz liczby dodatnie i ujemne.

select * from table where col1 REGEXP '^[[:digit:]]+$'

Użyj 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;

Wtedy gdy zapytasz

select isnumber('383XXXX') 

-- zwraca 0

select isnumber('38333434') 

-- zwraca 1

Wybierz numer isnumber (mycol) mycol1, col2, colx z tablex; -- zwróci 1s i 0s dla kolumny mycol1

-- możesz zwiększyć funkcję, aby pobierać dziesiętne, notację naukową itp...

Zaletą używania UDF jest to, że można go użyć po lewej lub prawej stronie porównania "gdzie klauzula". to znacznie upraszcza Twój SQL przed wysłaniem do bazy danych:

 SELECT * from tablex where isnumber(columnX) = isnumber('UnkownUserInput');

Inną alternatywą, która wydaje się szybsza niż REGEXP na moim komputerze jest

SELECT * FROM myTable WHERE col1*0 != col1;

Spowoduje wybranie wszystkich wierszy, w których col1 zaczyna się od wartości liczbowej.

Wciąż brakuje tej prostej wersji:

SELECT * FROM myTable WHERE `col1` + 0 = `col1`

(Dodawanie powinno być szybsze jak mnożenie)

Lub najwolniejsza Wersja do dalszej gry:

SELECT *, 
CASE WHEN `col1` + 0 = `col1` THEN 1 ELSE 0 END AS `IS_NUMERIC` 
FROM `myTable`
HAVING `IS_NUMERIC` = 1

Polecam: jeśli wyszukiwanie jest proste, możesz użyć `

column*1 = column

` operator ciekawy :) działa i jest szybszy niż na polach varchar/char

SELECT * FROM myTable WHERE column * 1 = column;

ABC*1 => 0 (NOT EQU **ABC**)
AB15*A => 15 (NOT EQU **AB15**)
15AB => 15 (NOT EQU **15AB**)
15 => 15 (EQUALS TRUE **15**)

SELECT * FROM myTable WHERE sign (col1)!=0

Ofcourse znak (0) jest zerem, ale wtedy możesz ograniczyć zapytanie do...

SELECT * FROM myTable WHERE sign (col1)!=0 or col1=0

UPDATE: nie jest to w 100% wiarygodne, ponieważ "1abc" zwróci znak 1, ale "ab1c" zwróci zero... tak więc może to działać tylko dla tekstu, który nie zaczyna się od liczb.

Możesz użyć wyrażenia regularnego dla Mor detail https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Użyłem tego ^([,|.]?[0-9])+$. To pozwala na obsługę do liczby dziesiętnej i zmiennoprzecinkowej

SELECT
    *
FROM
    mytable
WHERE
    myTextField REGEXP "^([,|.]?[0-9])+$"

Możesz zrobić używając CAST

  SELECT * from tbl where col1 = concat(cast(col1 as decimal), "")

Odkryłem, że to działa całkiem dobrze

if(col1/col1= 1,'number',col1) AS myInfo

Spróbuj Podzielić / 1

select if(value/1>0 or value=0,'its a number', 'its not a number') from table