mysql 取 浮点运算_使用MySQL从一个字符串中返回一个浮点数

If I have something like table called mutable, for example, with this kind of data:

id size

1 100ml

2 30ml

3 6,7g

4 8,8kg

5 alfa 110v

6 beta 220v

How can I extract the number of a string and convert it to a float number. (At this case the , separates the decimal part from the integer one.

So, how can I perform a query to return something like this?:

id size finalSize

3 6,7g 6,7

4 8,8kg 8,8

2 30ml 30

1 100ml 100

5 alfa 110v 110

6 beta 220v 220

and this? (just the id`s):

id

3

4

2

1

5

6

P.S.: I need to do this with MySQL functions...

P.S.:2

I have tried search about match a regex using MySQL, but looks like there aren't functions to return the matched pattern.

Maybe if I create a function that replaces everthing that isn't a digit or , can solve the problem to me, but I couldn't find a way to do this. MYSQL REPLACE function doesn't use regex.

解决方案

Here is a solution based on a function: The function was readily available in one of thes posts. So I only changed adding a comma into the list as it works well :)

Query:

select id, size,

replace(STRIP_NON_DIGIT(size),',','.')

as final

from demo

;

Function:

CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))

RETURNS VARCHAR(255)

BEGIN

DECLARE output VARCHAR(255) DEFAULT '';

DECLARE iterator INT DEFAULT 1;

WHILE iterator < (LENGTH(input) + 1) DO

IF SUBSTRING(input, iterator, 1) IN (',', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN

SET output = CONCAT(output, SUBSTRING(input, iterator, 1));

END IF;

SET iterator = iterator + 1;

END WHILE;

RETURN output;

END//

Results:

| ID | SIZE | FINAL |

--------------------------

| 3 | 6,7g | 6.7 |

| 4 | 8,8kg | 8.8 |

| 2 | 30ml | 30 |

| 1 | 100ml | 100 |

| 5 | alfa 110v | 110 |

| 6 | beta 220v | 220 |

As you mentioned yourself, MYSQL doesn't have the regex_replace like Oracle. Unless you want to use multiple number of replaces (wouldn't want to assume how many), a funciton would come in handy for you.