从MySQL中的字符串中删除数字 [英] Removing numbers from string in mysql
本文介绍了从MySQL中的字符串中删除数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个像"FALL01"这样的字符串,我必须从这样的字符串中删除数字,因此输出应类似于Fall
,spring
之类.请让我知道如何使用sql query删除数字.以下是我的示例表.谢谢
I have a string like "FALL01" and i have to remove number from such string so output should look like Fall
, spring
and the like. Kindly let me know how can i remove number with sql query . Following is my sample table. Thanks
Season
------
FALL01
FALL05
Spring01
Summer06
推荐答案
我建议您为此手动创建一个User Define Function
.这是一个很棒的教程,您可以使用
I suggest the you manually create a User Define Function
for this. Here's a great tutorial that you can use
代码段:
DELIMITER $$
DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$
CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50))
RETURNS INT
NO SQL
BEGIN
DECLARE ctrNumber varchar(50);
DECLARE finNumber varchar(50) default ' ';
DECLARE sChar varchar(2);
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);
ELSE
SET finNumber=CONCAT(finNumber,'');
END IF;
SET inti=inti+1;
END WHILE;
RETURN CAST(finNumber AS SIGNED INTEGER) ;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;
创建函数后,您现在可以轻松地从字符串中删除数字,例如
once the function has been created, you can now easily remove the numbers from string, example
SELECT uExtractNumberFromString(Season)
FROM TableName
这篇关于从MySQL中的字符串中删除数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文