mysql enquery get last number_如何從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

32

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

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

4

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

4

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

1

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

1

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

0

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函數以正確的格式顯示它。