How can I split text and number in MySQL?

Question

I have the below data and want to separate the float value and string value using MySQL.

number_column
122.33
123.44
15.44
x-mas
lax
closed

I just want float value. I want to remove the string value from there.

Answer 1

You can use a regular expression to match the characters that appear in floating point numbers.

SELECT *
FROM yourTable
WHERE number_column RLIKE '^[-0-9.]+$'

Note that this simple regexp doesn't actually check for valid numeric syntax; it will allow 1.2.3 or 3-2. If you need a more accurate regular expression, see Regular expression for floating point numbers