How to select only records that have float numbers in column and not null (MYSQL)

Question

In my data table I have following values for field lat are-

20.235412,20.125412,null,mg road 542365,20.236541,null,ahemdabad 254156,

From above values I want 1st, 2nd and 5th so I tried this query-

SELECT com_name,lat,lng,alias FROM company_details WHERE lat IS NOT NULL AND TRIM(lat) <> '' AND IsNumeric(lat) = 1

and this

SELECT com_name,lat,lng,alias FROM company_details WHERE lat IS NOT NULL AND TRIM(lat) <> '' AND not lat like '%[^0-9]%'

but it's not working. Please help me.

possible duplicate of [Detect if value is number in MySQL](http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql) — Phil, Sep 11 '15 at 05:08

score 2 · Accepted Answer · answered Sep 11 '15 at 06:00

Have a look at REGEXP function -

SELECT '20.235412' REGEXP '^[0-9]+[.]{1}[0-9]+$';
=> 1

SELECT 'mg road 542365' REGEXP '^[0-9]+[.]{1}[0-9]+$';
=> 0

So, try something like this -

SELECT
  com_name, lat, lng, alias
FROM
  company_details
WHERE lat REGEXP '^[0-9]+[.]{1}[0-9]+$';

1 Answers1