如何从 MySQL 查询中的字符串中提取数值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/978147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you extract a numerical value from a string in a MySQL query?
提问by Teflon Ted
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"
price 是实际的数字价格，例如“9990”
price_display is the visual representation, e.g. "$9.99" or "9.99Fr"
price_display 是视觉表示，例如“$9.99”或“9.99Fr”
I've been able to confirm the two columns match via regexp:
我已经能够通过正则表达式确认两列匹配：
price_display not regexp format(price/1000, 2)
price_display 不是正则表达式格式(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 列中，所有这些都在更新语句的上下文中。我一直无法弄清楚如何。
Thanks.
谢谢。
回答by Vladiat0r
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('.00Fr');
#returns 1000
回答by Elwin
For me CASTING the field did the trick:
对我来说，CASTING 领域发挥了作用：
CAST( price AS UNSIGNED )// For positive integer
CAST( price AS UNSIGNED )// 对于正整数
CAST( price AS SIGNED )// For negative and positive integer
CAST( price AS SIGNED )// 对于负整数和正整数
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
IF(CAST(price AS UNSIGNED)=0,REVERSE(CAST(REVERSE(price) AS UNSIGNED)),CAST(price AS UNSIGNED))// 当价格以其他数字开头时修复
For more details see:
有关更多详细信息，请参阅：
回答by artlung
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:
这适用于您提供的示例数据：
'.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;
回答by Lev K.
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
回答by sbarrat
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 ;
回答by Richard Stelling
This is a "coding horror", relational database schemas should NOTbe 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, doubleor realinstead of a integer.
此外，您可以使用float,double或real代替整数。
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.
如果您需要存储货币数据，您可以考虑添加货币字段或使用系统区域设置功能以正确的格式显示它。
