1

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.

GarouDan
  • 3,743
  • 9
  • 49
  • 75
  • 2
    [What have you tried?](http://www.whathaveyoutried.com/) – John Conde Jan 18 '13 at 16:33
  • You don't seem to need a float, a decimal would do :) – bonCodigo Jan 18 '13 at 16:41
  • This is indeed not that simple as there is no function to retrieve the last matched regexp for instance. Google *mysql number from string*, people wrote a mysql function to extract numbers from strings. There is [this](http://stackoverflow.com/questions/5361457/mysql-return-matching-pattern-in-regexp-query), pretty complex... And you have to adapt the code to your case, since ',' is your decimal separator. – Déjà vu Jan 18 '13 at 16:47

4 Answers4

4

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.

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
1

try this

     SELECT size,
     @num := CONVERT(size, SIGNED) AS num_part,
     SUBSTRING(size, LENGTH(@num) + 1) AS rest_of_string 
     FROM your_table;
echo_Me
  • 37,078
  • 5
  • 58
  • 78
0

Great function bonCodigo!!!, but I want to report this modification in order to simplify the work:

DELIMITER $$
DROP FUNCTION IF EXISTS `EXTRACT_NUMBER`$$

DELIMITER $$
CREATE FUNCTION EXTRACT_NUMBER(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 CONVERT(replace(output,',','.'), DECIMAL) ;
END $$
xriz
  • 1
  • 1
  • Your version of the code doesn't simplify anything. Using your function, one can't alter the format. Basically, you've made a worse version of the accepted answer. – Mjh Oct 28 '15 at 10:15
0

Also, I've modify again to extract only one number if the string contains more than one. If exist some character o string pre-differenciate, you can call the function with EXTRACT_NUMBER(SUBSTRING_INDEX(Notas,'CHARS',-1)) being CHARS the pre-character(s)

DELIMITER $$
DROP FUNCTION IF EXISTS `EXTRACT_NUMBER`$$

DELIMITER $$
CREATE FUNCTION EXTRACT_NUMBER(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
        IF (SUBSTRING(input, iterator, 1) = ',' AND output = '') 
                THEN SET output = ''; 
            END IF;
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      IF (SUBSTRING(input, iterator, 1) NOT IN (',', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) AND output <> '') 
            THEN SET iterator = LENGTH(input);
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN replace(output,',','.');
END $$
xriz
  • 1