Teflon Ted Teflon Ted - 1 month ago 10
MySQL Question

How do you extract a numerical value from a string in a MySQL query?

I have a table with two columns: price (int) and price_display (varchar).

price is the actual numerical price, e.g. "9990"

price_display is the visual representation, e.g. "$9.99" or "9.99Fr"

I've been able to confirm the two columns match via regexp:


price_display not regexp
format(price/1000, 2)


But in the case of a mismatch, I want to extract the value from the price_display column and set it into the price column, all within the context of an update statement. I've not been able to figure out how.

Thanks.

Answer

This function does the job of only returning the digits 0-9 from the string, which does the job nicely to solve your issue, regardless of what prefixes or postfixes you have.

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#815

Copied here for reference:

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS digits;
DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);

  IF str IS NULL
  THEN 
    RETURN "";
  END IF;

  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END |
DELIMITER ;

SELECT digits('$10.00Fr'); 
#returns 1000