MySQL 如何在mysql中仅从字符串中获取数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37268248/
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 to get only Digits from String in mysql?
提问by Jay Doshi
I have some string output which contain alphanumeric value. I want to get only Digits from that string. how can I fetch this by query? which MySql function can I Use?
我有一些包含字母数字值的字符串输出。我只想从该字符串中获取数字。我怎样才能通过查询获取这个？我可以使用哪个 MySql 函数？
My query is like :
我的查询是这样的：
select DISTINCT SUBSTRING(referrerURL,71,6)
from hotshotsdblog1.annonymoustracking
where advertiserid = 10
limit 10;
Output :
输出 ：
100683
101313
19924&
9072&h
12368&
5888&h
10308&
100664
1&hash
101104
And I Want output like :
我想要输出如下：
100683
101313
19924
9072
12368
5888
10308
100664
1
101104
回答by Eugene Yarmash
If the string starts with a number, then contains non-numeric characters, you can use the CAST()function or convert it to a numeric implicitly by adding a 0:
如果字符串以数字开头，然后包含非数字字符，则可以使用该CAST()函数或通过添加 将其隐式转换为数字0：
SELECT CAST('1234abc' AS UNSIGNED); -- 1234
SELECT '1234abc'+0; -- 1234
To extract numbers out of an arbitrarystring you could add a custom functionlike this:
DELIMITER $$
CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50))
RETURNS INT
NO SQL
BEGIN
DECLARE ctrNumber VARCHAR(50);
DECLARE finNumber VARCHAR(50) DEFAULT '';
DECLARE sChar VARCHAR(1);
DECLARE inti INTEGER DEFAULT 1;
IF LENGTH(in_string) > 0 THEN
WHILE(inti <= LENGTH(in_string)) DO
SET sChar = SUBSTRING(in_string, inti, 1);
SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9');
IF ctrNumber > 0 THEN
SET finNumber = CONCAT(finNumber, sChar);
END IF;
SET inti = inti + 1;
END WHILE;
RETURN CAST(finNumber AS UNSIGNED);
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;
Once the function is defined, you can use it in your query:
定义函数后，您可以在查询中使用它：
SELECT ExtractNumber("abc1234def") AS number; -- 1234
回答by Martins Balodis
Based on Eugene Yarmash Answer. Here is a version of the custom function that extracts a decimal with two decimal places. Good for price extraction.
基于 Eugene Yarmash 的答案。这是一个自定义函数的版本，它提取带有两位小数的小数。有利于价格提取。
DELIMITER $$
CREATE FUNCTION `ExtractDecimal`(in_string VARCHAR(255))
RETURNS decimal(15,2)
NO SQL
BEGIN
DECLARE ctrNumber VARCHAR(255);
DECLARE in_string_parsed VARCHAR(255);
DECLARE digitsAndDotsNumber VARCHAR(255) DEFAULT '';
DECLARE finalNumber VARCHAR(255) DEFAULT '';
DECLARE sChar VARCHAR(1);
DECLARE inti INTEGER DEFAULT 1;
DECLARE digitSequenceStarted boolean DEFAULT false;
DECLARE negativeNumber boolean DEFAULT false;
-- FIX FIND_IN_SET cannot find a comma ","
SET in_string_parsed = replace(in_string,',','.');
IF LENGTH(in_string_parsed) > 0 THEN
-- extract digits and dots
WHILE(inti <= LENGTH(in_string_parsed)) DO
SET sChar = SUBSTRING(in_string_parsed, inti, 1);
SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9,.');
IF ctrNumber > 0 AND (sChar != '.' OR LENGTH(digitsAndDotsNumber) > 0) THEN
-- add first minus if needed
IF digitSequenceStarted = false AND inti > 1 AND SUBSTRING(in_string_parsed, inti-1, 1) = '-' THEN
SET negativeNumber = true;
END IF;
SET digitSequenceStarted = true;
SET digitsAndDotsNumber = CONCAT(digitsAndDotsNumber, sChar);
ELSEIF digitSequenceStarted = true THEN
SET inti = LENGTH(in_string_parsed);
END IF;
SET inti = inti + 1;
END WHILE;
-- remove dots from the end of number list
SET inti = LENGTH(digitsAndDotsNumber);
WHILE(inti > 0) DO
IF(SUBSTRING(digitsAndDotsNumber, inti, 1) = '.') THEN
SET digitsAndDotsNumber = SUBSTRING(digitsAndDotsNumber, 1, inti-1);
SET inti = inti - 1;
ELSE
SET inti = 0;
END IF;
END WHILE;
-- extract decimal
SET inti = 1;
WHILE(inti <= LENGTH(digitsAndDotsNumber)-3) DO
SET sChar = SUBSTRING(digitsAndDotsNumber, inti, 1);
SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9');
IF ctrNumber > 0 THEN
SET finalNumber = CONCAT(finalNumber, sChar);
END IF;
SET inti = inti + 1;
END WHILE;
SET finalNumber = CONCAT(finalNumber, RIGHT(digitsAndDotsNumber, 3));
IF negativeNumber = true AND LENGTH(finalNumber) > 0 THEN
SET finalNumber = CONCAT('-', finalNumber);
END IF;
IF LENGTH(finalNumber) = 0 THEN
RETURN 0;
END IF;
RETURN CAST(finalNumber AS decimal(15,2));
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;
Tests:
测试：
select ExtractDecimal("1234"); -- 1234.00
select ExtractDecimal("12.34"); -- 12.34
select ExtractDecimal("1.234"); -- 1234.00
select ExtractDecimal("1,234"); -- 1234.00
select ExtractDecimal("1,111,234"); -- 11111234.00
select ExtractDecimal("11,112,34"); -- 11112.34
select ExtractDecimal("11,112,34 and 123123"); -- 11112.34
select ExtractDecimal("-1"); -- -1.00
select ExtractDecimal("hello. price is 123"); -- 123.00
select ExtractDecimal("123,45,-"); -- 123.45
回答by Desert Eagle
I have rewritten this for MemSQL Syntax:
我已经为 MemSQL 语法重写了这个：
DROP FUNCTION IF EXISTS GetNumeric;
DELIMITER //
CREATE FUNCTION GetNumeric(str CHAR(255)) RETURNS CHAR(255) AS
DECLARE i SMALLINT = 1;
DECLARE len SMALLINT = 1;
DECLARE ret CHAR(255) = '';
DECLARE c CHAR(1);
BEGIN
IF str IS NULL
THEN
RETURN "";
END IF;
WHILE i < CHAR_LENGTH( str ) + 1 LOOP
BEGIN
c = SUBSTRING( str, i, 1 );
IF c BETWEEN '0' AND '9' THEN
ret = CONCAT(ret,c);
END IF;
i = i + 1;
END;
END LOOP;
RETURN ret;
END //
DELIMITER ;
SELECT GetNumeric('abc123def456xyz789') as test;
回答by Estefy
I suggest using a pivot table (e.g., a table that only contains a vector of ordered numbers from 1 to at least the length of the string) and then doing the following:
我建议使用数据透视表（例如，一个只包含从 1 到至少字符串长度的有序数字向量的表），然后执行以下操作：
SELECT group_concat(c.elem separator '')
from (
select b.elem
from
(
select substr('PAUL123f3211',iter.pos,1) as elem
from (select id as pos from t10) as iter
where iter.pos <= LENGTH('PAUL123f3211')
) b
where b.elem REGEXP '^[0-9]+$') c
回答by Aleksey Kuznetsov
Here is my improvement over ExtractNumber function by Eugene Yarmash.
这是我对 Eugene Yamash 对 ExtractNumber 函数的改进。
It strips not only non-digit characters, but also HTML entities like &#[0-9];, which should be considered as non-digit unicode characters too.
它不仅会去除非数字字符，还会去除 HTML 实体，例如&#[0-9];，它们也应该被视为非数字 unicode 字符。
Here is the code without UDP on pure MySQL <8.
这是纯 MySQL <8 上没有 UDP 的代码。
CREATE DEFINER = 'user'@'host' FUNCTION `extract_number`(
str CHAR(255)
)
RETURNS char(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE tmp VARCHAR(255);
DECLARE res VARCHAR(255) DEFAULT "";
DECLARE chr VARCHAR(1);
DECLARE len INTEGER UNSIGNED DEFAULT LENGTH(str);
DECLARE i INTEGER DEFAULT 1;
IF len > 0 THEN
WHILE i <= len DO
SET chr = SUBSTRING(str, i, 1);
/* remove &#...; */
IF "&" = chr AND "#" = SUBSTRING(str, i+1, 1) THEN
WHILE (i <= len) AND (";" != SUBSTRING(str, i, 1)) DO
SET i = i + 1;
END WHILE;
END IF;
SET tmp = FIND_IN_SET(chr, "0,1,2,3,4,5,6,7,8,9");
IF tmp > 0 THEN
SET res = CONCAT(res, chr);
END IF;
SET i = i + 1;
END WHILE;
RETURN res;
END IF;
RETURN 0;
END;
But if you are using UDP's PREG_REPLACE, you can use just following line:
但是，如果您使用的是 UDP 的 PREG_REPLACE，则可以仅使用以下行：
RETURN PREG_REPLACE("/[^0-9]/", "", PREG_REPLACE("/&#[0-9]+;/", "", str));
回答by Sharad Gautam
Try this in php
在 php 中试试这个
$string = '9072&h';
echo preg_replace("/[^0-9]/", '', $string);// output: 9072
or follow this link to do this in MySql Refer the link
或按照此链接在 MySql 中执行此操作 参考链接
回答by Hytool
Try, Query level,
尝试，查询级别，
SELECT CAST('1&hash' AS UNSIGNED);
for PHP,
对于 PHP，
echo intval('13213&hash');
回答by JeremiahM
For any newcomers with a similar request this should be exactly what you need.
对于任何有类似要求的新人，这应该正是您所需要的。
select DISTINCT CONVERT(SUBSTRING(referrerURL,71,6), SIGNED) as `foo`
from hotshotsdblog1.annonymoustracking
where advertiserid = 10
limit 10;
