如何从 MySQL 查询中的字符串中提取数值? [英] How do you extract a numerical value from a string in a MySQL query?

问题描述

我有一个包含两列的表格:price (int) 和 price_display (varchar).

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

price 是实际的数字价格，例如9990"

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

price_display 是视觉表示，例如$9.99"或9.99Fr"

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 不是正则表达式格式(价格/1000, 2)

price_display not regexp format(price/1000, 2)

但在不匹配的情况下，我想从 price_display 列中提取值并将其设置到 price 列中，所有这些都在更新语句的上下文中.我一直无法弄清楚如何.

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.

谢谢.

推荐答案

这个函数只返回字符串中的 0-9 位数字，这很好地解决了你的问题，不管是什么前缀或后缀你有.

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/query.php?&bw=1280#815

复制到这里以供参考:

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

这篇关于如何从 MySQL 查询中的字符串中提取数值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！