mysql 字符串提取金额,如何从MySQL查询的字符串中提取数值？

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.

解决方案

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.

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