--现有字符串'23456中国3-00=.,45',想得到结果2345630045 --方法一:translate函数 select translate('23456中国3-00=.,45','0123456789'||'23456中国3-00=.,45','0123456789') from dual;--方法二:自定义函数。 create or replace function f_filter_str(var_str varchar) return varchar is var_str_new varchar2(2000); begin for i in 1..length(var_str) loop if ascii(substr(var_str,i,1))>=48 and ascii(substr(var_str,i,1))<=57 then var_str_new := var_str_new || substr(var_str,i,1); end if; end loop; return var_str_new; end f_filter_str; /select f_filter_str('23456中国3-00=.,45') from dual;--方法三:正则表达式 --oracle10g以上版本select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual; --方法四:create or replace and compile java source named stringutil asimport Java.io.*; import Java.sql.*;public class StringUtil { public static String filterStr2Num(String str){ String tmpstr = str; String savestr; String result = ""; for (int i=0;i<tmpstr.length();i++){ savestr = tmpstr.substring(i,i+1); if (StringUtil.isNumeric(savestr)){ result+=savestr; }} return result;}public static String filterStr2Str(String str){ String tmpstr = str; String savestr; String result = ""; for (int i=0;i<tmpstr.length();i++){ savestr = tmpstr.substring(i,i+1); if (!StringUtil.isNumeric(savestr)){ result+=savestr; }} return result;} public static boolean isNumeric(String str){ try{ Integer.valueOf(str); return true; }catch(Exception e){ return false; }} }然后: create or replace function Java_filterStr2Num(str In Varchar) return varchar2 as LANGUAGE JAVA NAME 'StringUtil.filterStr2Num(java.lang.String) return java.lang.String';create or replace function Java_filterStr2Str(str In Varchar) return varchar2 as LANGUAGE JAVA NAME 'StringUtil.filterStr2Str(java.lang.String) return java.lang.String';再然后 Select java_filterStr2Num('254名字性345345别介绍') From dual; 取数字 Select java_filterStr2Str('254名字性345345别介绍') From dual; 取文字
--方法一:translate函数
select translate('23456中国3-00=.,45','0123456789'||'23456中国3-00=.,45','0123456789') from dual;--方法二:自定义函数。
create or replace function f_filter_str(var_str varchar) return varchar
is
var_str_new varchar2(2000);
begin
for i in 1..length(var_str) loop
if ascii(substr(var_str,i,1))>=48 and ascii(substr(var_str,i,1))<=57 then
var_str_new := var_str_new || substr(var_str,i,1);
end if;
end loop;
return var_str_new;
end f_filter_str;
/select f_filter_str('23456中国3-00=.,45') from dual;--方法三:正则表达式
--oracle10g以上版本select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual;
--方法四:create or replace and compile java source named stringutil asimport Java.io.*;
import Java.sql.*;public class StringUtil
{
public static String filterStr2Num(String str){
String tmpstr = str;
String savestr;
String result = "";
for (int i=0;i<tmpstr.length();i++){
savestr = tmpstr.substring(i,i+1);
if (StringUtil.isNumeric(savestr)){
result+=savestr;
}}
return result;}public static String filterStr2Str(String str){
String tmpstr = str;
String savestr;
String result = "";
for (int i=0;i<tmpstr.length();i++){
savestr = tmpstr.substring(i,i+1);
if (!StringUtil.isNumeric(savestr)){
result+=savestr;
}}
return result;}
public static boolean isNumeric(String str){
try{
Integer.valueOf(str);
return true;
}catch(Exception e){
return false;
}} }然后:
create or replace function Java_filterStr2Num(str In Varchar) return varchar2 as
LANGUAGE JAVA
NAME 'StringUtil.filterStr2Num(java.lang.String) return java.lang.String';create or replace function Java_filterStr2Str(str In Varchar) return varchar2 as
LANGUAGE JAVA
NAME 'StringUtil.filterStr2Str(java.lang.String) return java.lang.String';再然后
Select java_filterStr2Num('254名字性345345别介绍') From dual; 取数字
Select java_filterStr2Str('254名字性345345别介绍') From dual; 取文字
二 ‘大小限定’,是什么意思?是要限制选出来的字符的长度吗?
就是CWDLID小于6的 显示
[SQL] select CWDLID from jt_j_spxx where regexp_like(CWDLID,'^[0-9]+$') and CWDLID<6
[Err] ORA-01722: invalid number谢谢,还是不行啊