如何从MySQL查询中的字符串中提取数值?

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

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

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

价格是实际的数值价格，例如。“9990”

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

price_display是可视化表示，例如。9.99“9.99”或“fr”

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

我已经通过regexp确认了两列匹配:

price_display not regexp format(price/1000, 2)

price_display不regexp格式(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.

但在不匹配的情况下，我想从price_display列中提取值并将其设置为price列，所有这些都在update语句的上下文中。我不知道该怎么做。

Thanks.

谢谢。

6 个解决方案

#1

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.

这个函数只负责从字符串中返回0-9的数字，它可以很好地解决你的问题，不管你有什么前缀或后缀。

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

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

#2

One approach would be to use REPLACE() function:

一种方法是使用REPLACE()函数:

UPDATE my_table
SET    price = replace(replace(replace(price_display,'Fr',''),'$',''),'.','')
WHERE  price_display not regexp format(price/1000, 2);

This works for the examples data you gave:

这适用于您提供的示例数据:

'$9.99'
'9.99Fr'

Both result in 999 in my test. With an update like this, it's important to be sure to back up the database first, and be cognizant of the formats of the items. You can see all the "baddies" by doing this query:

我的测试结果都是999。有了这样的更新，务必首先备份数据库，并了解项目的格式。通过执行以下查询，您可以看到所有的“坏人”:

SELECT   DISTINCT price_display
FROM     my_table
WHERE    price_display not regexp format(price/1000, 2)
ORDER BY price_display;

#3

For me CASTING the field did the trick:

对我来说，投出这片土地是成功的:

CAST( price AS UNSIGNED ) // For positive integer

铸造(价格为无符号)//为正整数

CAST( price AS SIGNED ) // For negative and positive integer

铸造(价格为签署)//为负和正整数

IF(CAST(price AS UNSIGNED)=0,REVERSE(CAST(REVERSE(price) AS UNSIGNED)),CAST(price AS UNSIGNED)) // Fix when price starts with something else then a digit

如果(CAST(price AS UNSIGNED)=0，反转(CAST) (price (price) AS UNSIGNED))，CAST(price AS UNSIGNED) / Fix当价格以其他数字开始时

For more details see:

详情见:

https://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

#4

I create a procedure that detect the first number in a string and return this, if not return 0.

我创建一个过程来检测字符串中的第一个数字并返回这个，如果不返回0。

    DROP FUNCTION IF EXISTS extractNumber;
    DELIMITER //
    CREATE FUNCTION extractNumber (string1 VARCHAR(255)) RETURNS INT(11) 
        BEGIN
        DECLARE position, result, longitude INT(11) DEFAULT 0;
        DECLARE string2 VARCHAR(255);
        SET longitude = LENGTH(string1);
        SET result = CONVERT(string1, SIGNED);
        IF result = 0 THEN
            IF string1 REGEXP('[0-9]') THEN
                SET position = 2;
                checkString:WHILE position <= longitude DO
                    SET string2 = SUBSTR(string1 FROM position);
                    IF CONVERT(string2, SIGNED) != 0 THEN
                        SET result = CONVERT(string2, SIGNED);
                        LEAVE checkString;
                    END IF;
                    SET position = position + 1;
                END WHILE;
           END IF;
        END IF;
        RETURN result;
    END //
    DELIMITER ;

#5

Return last number from the string:

从字符串返回最后一个数字:

CREATE FUNCTION getLastNumber(str VARCHAR(255)) RETURNS INT(11)
DELIMETER //
BEGIN
    DECLARE last_number, str_length, position INT(11) DEFAULT 0;
    DECLARE temp_char VARCHAR(1);
    DECLARE temp_char_before VARCHAR(1);


IF str IS NULL THEN
    RETURN -1;
END IF;

SET str_length = LENGTH(str);

WHILE position <= str_length DO
    SET temp_char = MID(str, position, 1);

    IF position > 0 THEN
        SET temp_char_before = MID(str, position - 1, 1);
    END IF;

    IF temp_char BETWEEN '0' AND '9' THEN
        SET last_number = last_number * 10 + temp_char;
    END IF;
    IF (temp_char_before NOT BETWEEN '0' AND '9') AND 
           (temp_char BETWEEN '0' AND '9') THEN                 
        SET last_number = temp_char;
    END IF;

    SET position = position + 1;
END WHILE;

RETURN last_number;
END//
DELIMETER;

Then call this functions:

然后调用这个函数:

select getLastNumber("ssss111www222w"); print 222

选择getLastNumber(“ssss111www222w”);打印222

select getLastNumber("ssss111www222www3332"); print 3332

选择getLastNumber(“ssss111www222www3332”);打印3332

#6

This is a "coding horror", relational database schemas should NOT be written like this!

这是一个“编码恐怖”，关系数据库模式不应该这样写!

Your having to write complex and unnecessary code to validate the data.

您必须编写复杂和不必要的代码来验证数据。

Try something like this:

试试这样:

SELECT CONCAT('$',(price/1000)) AS Price FROM ...

In addition, you can use a float, double or real instead of a integer.

此外，您可以使用浮点数、双精度数或实数来代替整数。

If you need to store currency data, you might consider adding a currency field or use the systems locale functions to display it in the correct format.

如果需要存储货币数据，可以考虑添加货币字段，或者使用system locale函数以正确的格式显示它。

#1