求个sql，把查询字段的数字全replace掉的语句

[buller]

select top 2 cardno,replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(cardno,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),id
from mytable..tablename
order by replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(cardno,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') desc

实现这种功能的语句，能否简洁下？

[小小农民]

创建一个function ： DELIMITER $$ CREATE FUNCTION `TranslateStr`(str varchar(1000)) RETURNS varchar(1000) BEGIN set str = replace(str,'1',''); set str = replace(str,'2',''); set str = replace(str,'3',''); set str = replace(str,'4',''); set str = replace(str,'5',''); set str = replace(str,'6',''); set str = replace(str,'7',''); set str = replace(str,'8',''); set str = replace(str,'9',''); set str = replace(str,'0',''); return str; END; $$ DELIMITER 执行查询 select top 2 cardno,TranslateStr(cardno),id from mytable..tablename

[正怒月神]

如果你有固定格式，那么可以直接用substring截取。 虽然楼主这样写不美观，但是的确是比较简单了。

[秋的红果实]

SQL对字符串的处理效率并不是很高，建议先从库里取出字符串，然后在C#里，一个正则搞定，效率高还直观 [code=csharp][/ string str = "kskd233kUAU22%fjdj-中国jfkd99 00 dsUU999siiii"; string outstr=""; Regex Reg = new Regex(@"\D*"); foreach(Match m in Reg.Matches(str)) { outstr+=m.ToString(); } code]

[秋的红果实]

string str = "kskd233kUAU22%fjdj-中国jfkd99 00 dsUU999siiii";
string outstr="";
Regex Reg = new Regex(@"\D*");
foreach(Match m in Reg.Matches(str))
{
    outstr+=m.ToString();
}

[xdashewan]

对于单句sql这已经算简洁了，你还想简洁点可以自定义一个function