oracle 判斷字元是否為字母,oracle字元
create or replace function ischar(chr varchar2) return varchar2 is
ischr varchar2(5);
begin
select case
when ascii(chr) between 65 and 122 THEN
'true'
else
'false'
end
into ischr
from dual;
return ischr;
end;
oracle怎判斷查詢的列中是否包含字母
看看下邊的語句可不可以
select case when regexp_like(欄位名,'.([a-z]+|[A-Z])') then '包含字母' else '不包含字母' end
from 表名字
Regex函數:regexp_like
關鍵的參數是第二個:'.([a-z]+|[A-Z])'其中.表示匹配任何單字元,分行符號除外
[a-z]是小寫字母|或[A-Z]大寫字母
oracle 怎判斷數字字串中是否含有英文字母
簡單的:一條語句搞定,SqlPlus裡面select decode(length(replace(translate('字串的值','0123456789.',' '),' ','')),0, 'is number','is not a number') from dual; 麻煩點的:寫function在oracle資料庫中,create or replace function f_str_or_num(str varchar2) return varchar2 is
2 v_num number;
3 v_return varchar2(60);
4 begin
5 v_num:=to_number(str);
6 v_return:=str||' is a number string!';
7 return v_return;
8 exception when others then
9 v_return:=str||' is not a number string!';
10 return v_return;
11 end f_str_or_num; 然後調用select f_str_or_num('字串的值) from dual;