Oracle 判断字符串是否能转成数字。
Sybase 有自带的系统函数 isnumeric 来判断1个字符是否能转换成数字。
而Oracle没有这个函数,所以在sql语句使用to_number函数时有时候会因为数据问题导致系统出错。
解决方案有几个,
一, 新建1个自定义函数, 利用to_number, 能转换成to_number的就返回真, 转换出错的就返回假。
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str FLOAT;
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN 0;
END;
RETURN 1;
END IF;
END isnumeric;
二,这种方法不用新增自定义系统函数,利用translate函数,只需要加在where条件里。
例如下面这个sql语句
select t.instance
from sys.v$thread t,
sys.v$parameter v
where v.name = 'thread'
and (v.value = '0' or
t.thread# = to_number(v.value))
因为高亮字句的原因,v.value中存在不能转成数字的字符数据,所以是会报错的。
![Oracle 判断字符串是否能转成数字。 - 饥民 - 饥民2011](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAakAAAEbCAIAAADvTOxyAAAgAElEQVR4nO2de3gUVZ73f+EdZ3eeHWff19mdWUcf5xHfmXF2R3YeeW0yI7NeRlcUUQcHcHwdFRojjLCDjlxEUTBeCDqBVzSAuI7xghogwYSEXEi4BVAIYEK4RpCkIYQkTe7dAYL1/nE61dV1Tp2qvl/q+/H7xOpTp06dqk5/OFXdfUJfunpZcnJy5s+f7wYAABtA+5p6WZj7GhsbS0tLS0pKKioqEz9lZWXr169vbGyM92kEACQZevdt3Lhx27Ztp0+3JEv21daWlpbG+zQCAJIMvfuKiopOt7QkV8rKyuJ9GgEASQbtbephYe6rrKxsaW1NrlRWVsb7NAIAkgyB+1pb25MrcB8AIFhE7mtvT67AfQCAYBG4r73dHUKISP1ppWYEA/cBAIJF4L6A1cboGyJSf5rs0kKdoDB13/Dhw+WFwgoh1JTvToV/CACIMZz7qqrcHR3CEJHpKoEiDcp1FcJJZVWV7qiGm6FWk9TXtiZc1m0rbIffUK1p1CAAIAbQnsYeFua+qqqqjo5OYYhIvkr7UxLTCsGminOfilY0RpaxPioM1o+6Qoz7AEgcRO7r7BSGiIzKtavk1XQY7SuoGLlPOL4T1uGNJjSUbnN+KCcfP0o6BgCIMXr3VVRUNDW5hCEio0LtKq3arLcTTioqKnRHJR+UaVUlrCAvMbp65XdnZaQJ/QEQF/TuK6+o+PpEkzBEZGUVX01ys49h1KzFlHPuY5iOwqy4z3QroytZnVvlhP08AgCCQzDuO9HkEoaIrKySVFPXyusEG37cx5CMyHQPeRPxq/gWrCwb9Qq+AyC+iK55XaeEISIrq3RjOmE1XX2jZi1G4r4wx328B/k6uhL57nSbwIAAxAuqOdHDMui+StepZmGIyMoqo2pMhbpqkjatp6JC/Pm+oMZ9ukLhOE44NpTU15YYaQ4GBCAuCNx36lSzMERkZRVfjVlPXaVWkDQYVCTuszgQ4+uYuswtGgZK9si3L+wSACA26N1XWVnZ3NzCYvoehbamcFkX3VbCkhAS8nfahOM+vkQyZFMXTOvzopQ3DgCIKrT7RDeLfw6rltbkCu8+oyGYlaGWcHgoqSMc2WHcB0CCI3Bfa2tbcgVzGQAAgoV3X1Vrmzu5UllZdezYsXifSQBAMiFwX1ubO7lSWVlVBAAAwaB3HwAA2AGB+xQpOTk58gqKomzevNm0DgAAxBHa9XU3C9wHALAPcB8AwI5ExX0AAJDgwH0AADsC9wEA7Ah9cbybBff7AAD2IUT3lZSUCD8yU1JSokTOfcMzVqgJp4WIdCZSzUapSwCAoAjRfUbVWHlkx30xEw0RhbaXoID7AEgE6PPjXSzBum9g4MLFi74MDFwYGDjv9fSYbs7QDuhUERiN8oxKdOV8m8K9GJ6IQKz0XH44QXVJfjZM9xX+ABkAuxG6+7zePq+3z+vt9Xp7PZ7ers62U41HrbiPf9EaFZo+FG7OHgq3lWN93CcRtOkRybskPzrhYVpvHACgErr7+vq69H9uraHOuvvkwxlT9wnHU0b7Mu0SIxruk9TkC4WjOeGCZFu4DwAr0OfHuliCdV9399nOzna3+3R7W3PrGZfrxJGGgzVB3e+zIi9+leT1b7qtnMi6T7dKMjQzHSFacZ/FngMAGKG772Tjka8O7zu8/4tDdTsP1u6oq9m87/MKK+4L6qJVuEp+3afdXLcv+aFp3acuC2//WR/3SQ5Z2Pmg3Gd66gAARoTuvrqaTTuq8reWfrKl9OMtpR9v3rBqW3medfcZXdvyL355uWRzXbml08G916F7KOyScFlyHSrvvPX2TY8dAGAE7TzWxRLBz/cBAECCE6L7AAAgqYH7AAB2JCruw/d5AQAJDtwHALAjcB8AwI7Qjq+6WHC/DwBgH+A+AIAdgfsAAHaEtn/VyYL7fQAA+wD3AQDsSHK4L/W+rGo6Z4x8CtWIdCA281QDkJjQ9oZOloS935di1lNEMyYoIhtG202htQ9jgtQg0d1nZVITvia/oRWB6uasN1rma8pb45flu9buReEGaLo2hbuzUmjUEznCfgKQjCS6+xSz2UAlU+BJ6vCQweR9FgtDa9OoslpitFNeZ8F2PmR5QXkgNaDqhk6WhL3fZ+Q+o5LQ3KeYXYqaDrKEDRoty/1lurlRz40GgyGI2wi4D6QGcJ8AXhDWB2vCClYGgGG6T943uA8AHXCfD4kU+MGU0bKRlayMGeXCTUz3mR4+AAkLVR/tZEnM+32S9zrk87Yb3RM00p/wmlFdZaWm0Ys/qEK1EdNl3sjyQq3+JAdrBaPDD6EpAOJForsvEYjGqzrFTIFBH0g64D4ZYY6P7APOD0g6aNvRDpaEvd8HAAARB+4DANgR2nq0gwXuAwDYh6i4DwAAEhy4DwBgR2jrkQ4WuA8AYB+i4j7c7wMAJDhwHwDAjtCWIx0scB8AwD5ExX0g2uB7FACECdyXfCTg1+wSqjMAWIG2HO5ggfvCRPflX6Nlvqa8NX5ZWFMRTShgNLMLX9moh/wejdqUlFvpqimS8wlACETFfTa836d7hQdbGFqb2rV8BaMdCRVptCOd/iRtWum8cO/WsX7qADAF7osYwsGIkTWsKECuDysaMhqOSTpv6j6LbVqvaR24D0QQ2nz4LAvcFyn4l6jRC9Wi/oIaP0ZqjGY67pMfSMiHLEd+PgGwTlTcZ0MkcglqjCZs1nTMmFDuC1P3cuA+ECngvsggvL5TV1mpKdyW3zyENrWrJJeipljcl6RXRnsPipA3BEAL3Bd1ovFa5b0T8V0AkNrgfl8UCX+ME9ReorcLAFIPuA8AYEdo06GzLHAfAMA+RMV9AACQ4MB9AAA7QlWHzrLAfQAA+xAV9+F+HwAgwYH7AAB2hKoOnmWB+wAA9iEq7gMAgAQn+dw3PGMFS3y7kSzQ8BrTCqZ1wuyArv2o7g4Ai1DlQTdLsrhPGdRfvHuRHPCisVIS1T7AfSARiIr7InK/Tx3fMc1pH+pWCesrgSNEYblcoMLpT0y7KumSpP8WuxQCvHe00dbRDdDUh8Jyo2b5QmEfInJoAIRD4rpP0QhL5y/JgnBZ2IhiYfxI3LR0VrrKtynssGLsa3mXgpoiweK4T+dBvly41kohrnlBYkKVB9wsKeA+KyqRjBx5wnQfvyOJ+6I39NMht6HRslrCDxt15VbaASDuRMV9EUQ+spOPtoTlwcqFrE0UbHEoajrus9KfMKfGCtN98gbhPpAs0MYDbpZUcp/puI+vaYTQfbx3Iui+WI77+PtxcB+wCcnnPv4SUn7vTCiXoEZb/PBK5z4rXeIvfo0qhHyurKO9PjVdNr28Naps1AIAiUBU3IfvdQAAEhyqOOBmgfsAAPYB7gMA2JGouA8AABIcqqh3s8B9AAD7APcBAOxIVNyXyPf7VtQIAgCwG1Re384C9wEA7APcB/cBYEei4r5EBu4DACiKQuX721ngPgCAfUg195l+K1arPCKKnvti/BVdIyTzUJnWBCCFiYr74nu/L0HcZ6UzMcDK5KPChwCkNlS2v50lMd0X7OwsimX3qeJjy8FOT2+0bDpllmSSq2iIMrLukzwdSgKIHgDrJLr7VEynw+N9JEToPiUK09MLH8o7b9SmljDnrA/HfUb95OfmAiDxiYr7IohwlBeOPqLkPtPRqGTzWE7hF86UonL3RaqHAMQGKq1rZ0lA95m+2CLoPsXy9PSKZuZReT+FnZF33pQw56yH+wBgpJr7Qr7mVSxPT8/vJbLuw7gPgBgQFfdF6b0O/sKQX1bM9Cdxn2Jhenpdr6z007SyriSMs2WJcCaR5/sZs6t1ACJLorsv4sT4My4AgMQE7oP7ALAjCX2/LxrgO20AAEVRaENdGwvcBwCwD3Af3AeAHYmK+5Llfh/cB4BtoQ21bSzRcN/584cQBIlvhP/ex71XcQ/chyApHrhPmKi4TyXuh4cgCNwnDJXUtrHAfQiSkoH7hIH7ECTFA/cJExX34X5ftJ4tIiKKZfvsYVR3mnSnLqpnQ/vl6Ej1kP8CO9x3/vwhKv6yjQXui91Jt/zi4WtGW0PRdl8cNZoU7mOB+2IQuC/mZzyQYGuqMtJuqz4UlvM6Myo8H/iyiYb4hMdu/WzojlG4rG1NeDimJ8TofPINCnfKRzuUEy7rTKcr4TfRlfOi1PZEOHdRpJ7T5E1U3KcS98NLzFi3CV+T9yBfLlxrpZBXiXUrWalpdEQWz4zw2CVHpNOf9c2F51PYQ+udF8qLXyt8KNycl6BRx+A+YeC+eJz0CF3zyl94RkriC+VtWu9tZI9Ivm1o7hPWNDpLVvofpvt0sx/ylSWb8xNEGnUM7hM/lXBfHE56rNwnbzA0KQjbjNm4z3rnz1t2X1DPkVH/rTynqrPUh0YDt2DHffJ+wn3iU7R+XysL7vfF7qQbu8a0ZszcJ9xjNI49Uu7jR7JxdJ/w2dQN0CLrPoz7gg3cF6fzLrrIMrKVusp0WdimpFAnDl2b5yMtPvmxm4pPeOzaU0eWEbYsORvaysK+CXurKzS6tpXHqLKuRNIluE8YuA8RJ3ruS/Yk/jnR9RCfcRGfpWi4TyXuh4eEFqWgICLLKRm4LzUC9yHiMIWF8zPkmF6lIkEF32kTBu5DDIMRX2oE7hOGiva1suB+H4KkZOA+YeA+BEnxwH3CRNd9AACQmFDh3lYWm/ydNgAAUOA+AIA9gfsAAHaECveeYcH9PgCAfYD7AAB2BO4DANgR+mzPGRbc7wMA2Ae4DwBgR+A+AIAdiYr7cL8PAJDgwH0AADsC9wEA7Ait23OGBff7AAD2Ae4DANgRuA8AYEeooOYMC9wHALAPAvcBAEDKE+C+eIsYAABiBBXUtLDk5OS4DSAiYaEEo6YAACARMHefRGcQHwAgSTFxn6nUID4AQDJC+btbWHj3WRzTQXwAgKTD0H1B3cuD+AAAyUWA++L9pjMAAMQIvfvi7WIAAIgFevc1NjaWlpaWlJRUVFTaM2VlZevXr29sbIz3UwMAiCK0dlcLC3Pfxo0bt23bdvp0i52zr7a2tLQ03k8NACCK6N1XVFR0uqUFKSsri/dTAwCIInr3VVZWtrS2IpWVlfF+agAAUUTgvtbWdhbKIMqgmZ/MUkvsE7gPgNRG5L72dhbKIEVRpuQ/SpNo5qpZarkdAvcBkNoI3Nfe7mahyaQoSkV/7oqev1yd+QN6iGZ9MEtdayVEJC8UVjBaK68c2cB9AKQ2tGbXaRbVff51TnJfPPVy54Tn2++f03LvjGOj/s35j+NG/uC1O0b+9VeON+/47aonHt/80UcnNR8HkXwbRPvdD+2C5Hsjuu+K8F8dCerLJyofrvp08pRpdfvrN23ZOnnKtA9XfcrXgfsASG0491VVuTs6WGgiuQaOvNB+/+yWe2d8NerJFcNzpt584oXpfQumnH9uYteMcQf/eMf7o2768KXMYw0N6la+bYl0P/kIy9VC7YJAbVwLRsvCzH/p1TnPPj977vPzX3rF4/V29/ToKlRWVcX7qQEARBG9+6qqqjo6OlnoETpwbsfslnufdN31p/W/ypl6c+sLUy+OHnrx6XEXtxafW/RU14jLTt72szdGXr9q6ZtnWs74NyQyWtDV0RlNW85vKGzECF1NXb46dnzylGnTn5zZ5DrZ1+fhK1TBfQCkNLT6i9Msfvd1drLQQ7Spd/WTrtFTj//nvKd+eeKF6RdHD70wjC4s/LPS7z1XtKr9J3T6mrTdw6984eYbd23f3tHpk5G/hcBl9lBbh6+sraNdq6tstLlRTV3yPyucPGXa5CnTdnz+RWdXF18B7gMgtdG7r6KioqnJxUIP0jvtmVOO3z7xyM2v3Hd934IpF371vf5f0Ln5j3/T1+P95O2Wa8h1NR346aXPXnN57vIV/g1FqGtZBd2CusyX8A3Kd8TvTpfauv3TZjy9ZGnOG28tm/7k0wcOHubrVFRUxPupAQBEEb37yisqvj7RxEIP0IsnJ088cvMf6n+9MP2XF+ZN6h82pPdfqc95x8XTrq7n/+S6mk5cRYd/fMkLP/zuawte9G9IpC4LS9SHOmHpVukakcd6/XnzM6fPePpk8+nGJtfMZ+bNz3y1semkrk453AdASiMY951ocrHQOJp45OY/1N/4u32O52/7efeMcX3X/33ntXT2hsvOjvtVc/oVx6+iI1fQ7h99a9YV339l/nz/hiLUtayCbkFdVn9qNxQ2JRn08XvUpf3s2bOdnY2uk42uk+6OjvazZ3UVMO4DILURXfO6TrHQWPrD/hvH7nOM3nX9hJE/OPTwqO70y9w/pe65k7/p6ep+P+foFXTgcir/wbem/tu1K3OW+zckUpeFJepDbTlb5rcVVuPXSjYPIXAfAKkN775K16lmFrqPxu674fvTL6U76fdjf73ylhGnbr/2zP9OOzt9wkV3W/viFw9eTnX/MuTFf/7utPHji4tL/BuKUNeqFfiarERYTVuoa8poIZxUVODzfQCkMpT3+WkW1X2nTjWz0D1Eo+m+mfe9917uyuUrsqdPf+M3w/c4rjr48+8dufrbNVdeUv7Db734z9+dfNedH7yXe/jwYXVD0xCRbkG3illPW6hFW8i3ya8KIXAfAKmN3n2VlZXNzS1qcnPfz819f9u27c3NLYf2H8jNXjzvpl+/8LOrFlz+vdlX/tOfrrt2+oTxH+a+v29frct1qrm5xfQenLZxXSQVtOVW6oQffK8DgNRG4L6WllajuJpOVldt/uidd5e88uprmS/97e2VZaVlR49+derUaclWyRi4D4DURuC+1tY2BO4DILXh3VfV2uZGKiurjh07Fu9nBwAQLQTua2tzI5WVVUUAgNSFPt3ZzDL4Nyo7EARBUj5698mnP0EQBEmNwH0Igtgx9MnOZhbmvs7ObgRBkJQP3IcgiB2jd193d0+UkpHxdkbG207nsokT33rkkaXayDf803v19OhGenQjPbKRHi6nh8vpofIn/ruOrT3d1nX81Fkru87IeHvyYytC6ACCIKkXvft6evospuKZuZlEmUQVz8y1Uj8j421FUc6fG/B4znd197vdHrfbc7ql564758k3pEc3NvcpLKf6lJO9iqtXSXuwdNeump6evklv7hv90s6C7SdMd60oysDAN2oHPJ7zbrfH1dQ15u75e/d+af3AEQRJgYTuvkyiprFXHL/9h6uv+E4mkWn9xx5bMTDwzaJFmxYurFJz+HC7qfueyD3wry/sea9BefewsvKQsuygsrReoT+U5OWtPnjC/cPHq3654MvrppV+VF5v1AJz38DAxXPnBjx957u6zjHzupq6jh07O+bu+Xl5q+P+TCAIEsvQxzuaWZj7+vq8FpNJtOnSS7b+w//Y8ndD3kmjjXOfldd3OpedOzfgdntaWnqZdA4fbq+ra7nrznnyDRvPdF85vfIvG9p94jugLNmv0ISS1avXOJfVztjcn/+1cn1m3Y0zir48+LWwBabdxYu3ZP91My/fMXfPX716jfUDDz95eXnyCkQkXJbXRBDEekJxX/Vrry+/blgm0VKiPKI8oo+JMqUvwiVLlkyc+Faf5/wrr2xcsKB83ryyuXM3zJpVXFPTfMcdzy5ZskSybU75if85dfPbh5VlB5U3DyiL9yuv1Spp44s/+mTtxDf2XPvM7qd2KtO2K//wQMmcNys6Orr4Fph2+zwXurv7hfJNUvepq3ST5cTyWJCUSW9vwSRy5vd64t6T2MSq+1TfZRIVXf+/Do+9suynl2YSLSJaSrTUgvseeWRpd3d/k4tJp6229kxNTfOOHa7bfjtH7r5xb+z79aL97FJ35GsHXqtVXtmnpI0rWr16TfvZnrufK//JrF1Ttik/nb175BNrDh8+yrfAtPvaok0LF1Yx+Wb+x0MLiZ6/8UEm3zfHj59NVGQ8dO2tO+8ghVgmnevr8/b29mc5FCKFHOfrej29vecm+SoMmP72RMR90XBcb2/BJJ8/4/YaSJlXYG++k+J9MkPqLTmy6g2el0geS8QbDDZW3bf8umGN//eqnslXt9/7o8YRl+390d/nfXvI8jRiNswkk2veJUuW/PGh/+du98ybt2Hu3A0zZxU/9VTRn/+8bvOWE7fcMlPuvmue3Ppfxe1LDyg3vn5gxFNFQ2d+vmCPkja2kA3W1u1o/PbvPpu0Rbnt3ZZ/GZ+3fftOvgWmXbfb09LS0+TqKvyv2QuJlJUTM4ne/P2jj149chaR8tbE6UTvTJkqepLOTaKLWXW+Zyg/63xdr6cu6+KkfE9fn7cu66Ijq3+wmrn4+szcpx2+GQ3otCO+iP9OhGYfK1tFz2uJZsze3oJJ5FB/ZxI8g71l59B/JnVnNeInOdgGI9sBvfu83n5hMolOD/vH41d8p/7Sb+36uyGVQ9I+SaPFRJ/ce5/RJmoKCtZlZy9+8MHsltM9hw611dadqalp3rnDtXlzY0XFsZv+4y/Z2YsLCtYZbX7Jw+XZtRcX71fSJpSs+nTthJc3po0tHOr8LO2+ddOX73N39qWNXvPHKmVc6cAlo/I2bCjlW2DafflVdrm9gYlPmUDK/fQsEROfciMp/4ecRLW1dbrNPZ5zThoo8Hg1Jf1ZDl+Jp/6Cw3Gh3uPlqxklLy9PXoGI+GVtobZEOEfsYD/XOclppUuBxxvSVvWLHGZbWakTWqLXcoj9Cekcxq+39VmOEVn1+j7rzmrEDyrYBiP7LAfhvj3fHrJvSNqONKog+oxoFdHrRNtf/6t8B3l5q9nnS2Ybs3379hUrVixfvlzYwjUztkwpbF9Uq1zy4Ibnlub39vbt2bM37b51hzqUK57YeuvTJT+bu/eBjcpvVrZcfv8na9fm8y0w7Ta5uo4d7zh0qG311KcziZT7SbmXlFEB4ntl/IQ1a9aKTvoFByk0qDat5tTlGLtP6zjhhsIUqFc2jkX1PncvchARjciqN/wVF9TxlRA51w1u4ntsdAaCqeMcPLe+h05nwFa6oxC2XJ81YrDIa9SO7iiERxpCNP0hImd+X4HvoHyNDx6dZl8ezzonjXA41MMw3Lv22Pf7WiZHVr3Ht+gs8Hj5Z1nYPUdWPVt2ZNV7PPVZjsCTw51V9ovhdAZsKz8u/rnwNe7volWXWfn9CSq0avspFrn7tmRm5hJVE5URrSP6kGglUabZK83r7Z84caKiKHV1++v2a6nTPdyzd+/EiRP37t3HtzB+8d4bXq59ZZ/yi+f23DJz3bGvXV5vf9o9BQfPKpM2K0x895crV8344jdTPhWO+x544HVXUxd7g2XmzOKnnir687+PfpZIuZOUW0m5kZThfvHx4z7N2T/npG+y6r0xcB9pxnRqobaC13gkKP3tCZSa7x98r8ezzqnaMLAaX4e9TpwFbCHghWEy7gu+DvuNH/SXs8DjFTYi2iqgsqgd/VEIz0ZoER6F1kT+fdUvcvgspo68DM+S6BkcPJz6RQ7/vwRWB+DqVl6NXPyGsvRcGByX/7eFfy40dYIa90V04GnVfV5vfybRCqK1RB8QLSdabM19EyZM2FZdzVK9zfdfdXX1NvazupoWElVQdXX1hAniMdeysuOXPlqxYI/y3G7l58/svvf5cndHT9rd+QfOKn+sUpj47tmgfOfegideWr1z506+hXHjs44d6zh8qK3O9wbLyXcfnaG91FV+QQ8TZY6fIBHf4Nk/56SB/L7+LMeA06kQDRTUX3BE1H1a33ktXPPKS0S/Pf5/k3UjFKHF+DpW7BPy766pxfijELbsH9MJJCg+CuHZCC1GR8FXYAv5fQUa68nOEn/s9VkjnAXeAqfOWSQfvRY4yZFVz20VsHdr/6KYn0PT5yK0sxpmrLrvxRczhw4dmkm0mOgtor8SvUS0Ytgw0x2MGTOmsKioqLCwsKiwsLCoqKiwqKiwsKioiP2vsLBwcGHMmDFC97lae257fssNr+6f/YXy+Mb+UXMKv/hi15DRa/a7lQc2KmPLlXs2KI6c05ff9/G7H6w9erSBb2Hs2FcPHWqvq22pqWneuePku4/OmK2Kbzgp15HyE1J+TOOI3pg6VXTG+7OyzvmWCwbIcaHe4y1wKo6sfk/9hUmOb0h0v68g64LRwMH6Na92fBe++zyedVlZ9YG/xP6Bm9FvGF9HHTF5vf31Bet0o0VPwTrp69ZqHf0mmgXdUfAtS16l/LhPPQrh2QgtFtzHj/vM3Wd87JqbGKI6gqaYjwKGZuucvgvYoM9q4HHJLB8w7gvy/p2V3x/roY+2n2KRuG/ZsmVDhw598cVMr7d/S2Yme2N3xbBhpjf7vN7+W2+9NTc3V3eDb86c2bNnz57j+//s2XPm5L6fe+uttwrd5/X2F37eNGx66S/m7b3s4bLluWt37dqddteaOrdyf7lyT6lyZ4ly23rlsocrf/f02q6ubn7z++7NvHvMgrvufP6OO5697bY5szTicxI9TKT8mJTLSfknGiN6r8Pr7S9wDn7AhRT2avF4Bj/j4jzn9Y0H/XXUcmHCcZ9wSGhUIrKY+k+y7g5XwP0jw3+31RfYYEVHVn3g7vwtG//6au8SGo0aNJdUvoe+u0v5fYKj4Pfu0R7KDZN+z7VT4PHyR8EfaQgxuN+nH0ty9/usuE987GzoZ1THSILaQZ96S043WlTPqui5uP122XEJflvUQl+JY4SD29zSuZUel8Xo3ce/E1xQ8NnQoUNzcpaF9kZyenp6dXV1Tk7ObmNycnKqq6vT09MlnzFeVXHwxhlFtz+Zv3Ztfnv72SF3rP6y3S++mwqVG9cpQ27+sKZmj3Dz1avXqFk8fvx08olv2siRU0eOHEekfJ/uIXpu/PhgP+ecP8n3Eb9IvfHfN/jJFeI+v8J+eXTV+A0RW6W3t2CSIyv838BE+5xQtA9c7z7+W29Dhw7Nzy8I+UtzCxdm/bs1Hn98ivx7tXvrj28o37xz5xc9PX3T3tg95PZPh/z24yG/XTXk1lVDbvloyE0fjp7+sSHGC7EAAAboSURBVMVv5q58fIqT6OVx4/PyVu/d++WSx6fcQzR38GGcv2Y4iNFao2XJVkhKprs7fyIRkWPhl71x70zMj71uoSOsAzd3X/jZvXtPXt5qK9m9e4+8Kbe7o6Ojiy3v2lXDt8Amd7GSvXu/1JpO9xBBkNSO3n1xn1QLQRAkBtG7r7O7G0EQJOVDH1afYvH9raLOTgRBkJSP3n3ujg4EQZCUj959hwAAwAbo3QcAAHZA4D4FAABSHbgPAGBH6INtJ1ngPgCAfYD7AAB2BO7zQUSSh8JyozryVQCARIDe33aSJVXdR8bwNYXL2hL+p2S/Eek/ACAaSNzXkJ1ORETp2Q2KoijFGRpr+Mp85ZpHAnTtqIUZxZom9E0PbkREvnp8SQhHK/KR0IkhjPskkhWqFgAQRwzd15D9mM9VxRnp6hLTTkN2uuqf4gy91gLxS07bYoDBGrKzVZk1NDQEVFWKM4gy1nMlodnPuoB4FQr9aEWXwe4aABAD6P2tJ1kC3acdl6nyE7ivITs9/bHH0g3lJ2xH05SO4uzsBt3a4gzKWM+ViJYtHK3ZaE5nMeHITrgJACC5MHKf0C/+a9PAdQ3ZPvkFXLxS0M7SDgAVtaLeq3yJ5PCsXYcSdyXLD+W0Yz2+pnyPFnsLAIgZwbovozjgBp/Peqr8OIJxn2/QpyHgrqBBiWWMrkyFbjIaAPJmxKUuAMkI5W49yWL9mtd/yat5/8Hgnl8Q17zFGYEl/Jsopm+ryA/VwpsYpmM66w3KVwEA4ouR+zTS4t/rUCXkl5GhlkTtKCL3BZRo301hV8J8SXDHaeH2nMR0kitl7UMr19cAgETA0H3Gn3EZHPrRqFHqY99K4eUo/xmXgKYUf6H2ilhDevZRrsTkPRPdQRp8MIVfNlqrGNgTgz4AkhTK3epiSeHPNhs9lOjM6JpXrkvJfgEACUWKu08oIN11KF9HKDijS12+BVzkApD40HtbXCwp6T4AABAC9wEA7AjcBwCwI/S3LS4WuA8AYB/gvnBQv+gS8swyqQTOBkgmJO6L79xT/N4jVRIx2LkI/JZd+K984YcfjVrWzYgTPqE3KDobACQu9LfNLhbD73XEY+4pfu+RKokcDcXFDYM/I451h0Z8nBVag1E9GwBEHiP3RXvuKTn6vR+NUEnyvC6Tzn0AJBlG7ovv3FPmDg2tRPSS1s+7FcbLXjcu9j3MyFALffsa/C60/j6B8bWz6FJUMKOYoDPc3tktORqcYSw9PV3XSnGGbyvd96vVGweD7fgKIUqQlNC7m10sobsv8nNPxdJ9kUXgrIxiTbk6JYN/2i/dFGFGvdWVaOdMNDoubu+BC+t9M1Do3qMY3MrfQ82+tF/uTqZxNAB6jNwX37mnkveal3ef3mta3/jwvx1k3X1CYxp1RuI+1XryyrpxMa6LQdJj5L44zz3F7z1SJVHGgqGY6QT/nIQ07tOeX8POcAsN2emm7gsY98k7A0DyQe9uamKx8BmXSM09ZeWVkwSfceHQnR/tw4Abdvrj156kEY9N0J4xQVOBW/nu1/HnU7z3wRt3bKtRo0Tuy07n7j+qI9OA5xT6A0mMxH0AAJCywH0AADtC/72piQXuAwDYB7gPAGBH4D4AgB2hdzY1scB9AAD7APcBAOxIcO4z+QopAAAkCfROVROLyH3672z4P8TMfYUXAACSCCP3sa+AsUnv2Hc/oz0rJb4mBQCIHcbjvobsdMrIKM6Qf2c+cP4l/SxJ6le4Ar/TppsTCQAAYg2trGpi4a55+W+nCr80KpspxGBDfk4kAACIKUbuY8KycM2rn3/JaFYS7SiPnxMJAABiimTcp3Dvdfj/4sXgex0msyQVa+v66+AP2gAA4gytrGxksfAZF8EcmwFjOHVCOc3cSuuFf6hNMyeSyV8CAQCAKBCU+wAAIEWA+wAAdoTermxkgfsAAPYB7gMA2BG4DwBgR+A+AIAdgfsAAHaEVmxsZLHmvqD/zPjgVvjsHgAggZC7j3NWcYaFGQhgOgBAomPkPv0cVsxlDdnp6Y89li6Xn//bbGEChwIAogUt39jIYjaHlaL4bKTOwqJW035fTTf7i1oSaLGAOax8FTIyMLUBACBGGLtPYDGf9TTyU8d4AZM6c+M1XclgA/6GijP8MwBCfwCAqGPkPv0cVoNTs+hmIZVP6mdUEtRkfwAAEHloecUJFvP3Ovzv8apL/nt7DcXF+hlZiosNRoLCcR/cBwCIHXL3afFdAmcUByyqV8aB738Ua+7d8dfOwvt9/GT38CAAIFpYdx8AAKQOtKziBAvcBwCwD3AfAMCOwH0AADtCy8pPsMB9AAD7APcBAOwI3AcAsCOUU37CF7gPAGAb4D4AgB35/0Vsr6DVe7vaAAAAAElFTkSuQmCC)
而加入下面这些判断后,
就不会报错了,因为在执行to_number之前已经把不符合要求的数据筛掉。
执行了上面的条件后, 其实还有类似“-.6",".1" 这样的数据保留,其实无所谓,因为to_number是可以无错执行的。
关于translate函数可以参考:http://nvd11.blog.163.com/blog/static/2000183122012112605432545/
而Oracle没有这个函数,所以在sql语句使用to_number函数时有时候会因为数据问题导致系统出错。
解决方案有几个,
一, 新建1个自定义函数, 利用to_number, 能转换成to_number的就返回真, 转换出错的就返回假。
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str FLOAT;
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN 0;
END;
RETURN 1;
END IF;
END isnumeric;
二,这种方法不用新增自定义系统函数,利用translate函数,只需要加在where条件里。
例如下面这个sql语句
select t.instance
from sys.v$thread t,
sys.v$parameter v
where v.name = 'thread'
and (v.value = '0' or
t.thread# = to_number(v.value))
因为高亮字句的原因,v.value中存在不能转成数字的字符数据,所以是会报错的。
而加入下面这些判断后,
select t.instance
from sys.v$thread t,
sys.v$parameter v
where v.name = 'thread'
and translate(v.value,'a1234567890.-','a') is null --必须由数字和"."和"-"组成
and translate(v.value,'1.-','1') is not null --去掉"."和"-"后至少1个字符,也就是至少有1个数字啦
and length(v.value) - length(replace(v.value,'.','')) < 2 --"."的个数必须少于2
and (instr(v.value,'-',0)= 0 or ( --没有"-"
--或者只有1个"-"字符,而且用"-"开头
(length(v.value) - length(replace(v.value,'-','')) = 1)
and substr(v.value,1,1) ='-'))
and (v.value = '0' or
t.thread# = to_number(v.value))
就不会报错了,因为在执行to_number之前已经把不符合要求的数据筛掉。
执行了上面的条件后, 其实还有类似“-.6",".1" 这样的数据保留,其实无所谓,因为to_number是可以无错执行的。
关于translate函数可以参考:http://nvd11.blog.163.com/blog/static/2000183122012112605432545/