TO_NUMBER(decode(decode(lower(nvl(replace(translate(strTargetField, '$%', ' '),' ',''),0)),upper(nvl(replace(translate(strTargetField, '$%', ' '),' ',''),0)),1,0),1,nvl(replace(translate(strTargetField, '$%', ' '),' ',''),0),0)) numResult
例如:
public ActionResult GetXXX(string SheetName,string EqpCode)
{
string sql = $@"SELECT A.*, TO_NUMBER(decode(decode(lower(nvl(replace(translate(SEQUENCENO, '$%', ' '),' ',''),0)),upper(nvl(replace(translate(SEQUENCENO, '$%', ' '),' ',''),0)),1,0),1,nvl(replace(translate(SEQUENCENO, '$%', ' '),' ',''),0),0)) numResult
FROM 表名 A WHERE SHEETNAME='{SheetName}' AND EQUIPMENTCODE='{EqpCode}' ORDER BY numResult ";
var list = MaaSDBContext.Where<表名>(sql).ToList();
return OK(data: new { List = list });
}