BOOLEAN

isNumber

This function check type of column and value of column.

If column's value or type is number then return original number otherwise return 0 value as NUMBER type

select col1, h.isNumber(col1) 
from (
select 'A' col1 from dual
union
select '1' col1 from dual );

COL1 H.ISNUMBER(COL1)
---- ----------------
1 1
A 0
select col1, h.isNumber(sysdate) from dual;
H.ISNUMBER(SYSDATE)
-------------------
0
select col1, h.isNumber(7) from dual;
H.ISNUMBER(7)
-------------------
7

isNumber2

This function check type of column and value of column.

if column's value or type is number then return original number otherwise return your defined NUMBER type value

select col1, h.isNumber2(col1,999) 
from (
select 'A' col1 from dual
union
select '1' col1 from dual );

COL1 H.ISNUMBER(COL1,999)
---- ----------------
1 1
A 999
select col1, h.isNumber(sysdate,999) from dual;
H.ISNUMBER(SYSDATE,999)
-------------------
999
select col1, h.isNumber(7,999) from dual;
H.ISNUMBER(7,999)
-------------------
7

isString

This function check type of column.

if column's type is string then return original string otherwise return "" value as VARCHAR2 type

select col1, h.isString('A') from dual;
H.ISSTRING('A')
-------------------
A
select col1, h.isString(sysdate) from dual;
H.ISSTRING(SYSDATE)
-------------------
select col1, h.isString(7) from dual;
H.ISSTRING(7)
-------------------

isString2

This function check type of column.

if column's type is string then return original string otherwise return your defined VARCHAR2 type value

select col1, h.isString2('A','-') from dual;
H.ISSTRING2('A','-')
-------------------
A
select col1, h.isString2(sysdate,'-') from dual;
H.ISSTRING2(SYSDATE,'-')
-------------------
-
select col1, h.isString2(7,'-') from dual;
H.ISSTRING2(7,'-')
-------------------
-

isDate

This function check type of column.

if column's type is date then return original date otherwise return null date value as DATE type

select col1, h.isDate('A') from dual;
H.ISDATE('A')
-------------------
select col1, h.isDate(sysdate) from dual;
H.ISDATE(SYSDATE)
-------------------
15/05/21
select col1, h.isDate(7) from dual;
H.ISDATE(7)
-------------------

isDate2

This function check type of column.

if column's type is date then return original date otherwise return your defined DATE type value

select col1, h.isDate2('A',to_date('2000','yyyy')) from dual;
H.ISDATE2('A',to_date('2000','yyyy'))
-------------------
00/05/01
select col1, h.isDate2(sysdate,to_date('2000','yyyy')) from dual;
H.ISDATE2(SYSDATE,to_date('2000','yyyy'))
-------------------
15/05/21
select col1, h.isDate2(7,to_date('2000','yyyy')) from dual;
H.ISDATE2(7,to_date('2000','yyyy'))
-------------------
00/05/01

Usage

You can use like below for prevent invalid type oracle error.

		  insert into test ( a, b ) values ( systemid, h.isNumber(systemvalue) )

insert into test ( a, b ) values ( systemid, h.isNumber2(systemvalue,999) )

insert into test ( a, b ) values ( systemid, h.isString(systemvalue) )

insert into test ( a, b ) values ( systemid, h.isString2(systemvalue,'-') )

insert into test ( a, b ) values ( systemid, h.isDate(systemvalue) )

insert into test ( a, b ) values ( systemid, h.isDate2(systemvalue,sysdate) )

isChar

This function check character type number, special, English, Chinese , Korean, Japanese.

If not include any single string then return null string otherwise return original full string.

select h.isChar('%^',h.c_en) from dual;
H.ISCHAR('%^',H.C_EN)
-------------------
select h.isChar('%^abc',h.c_en) from dual;
H.ISCHAR('%^abc',H.C_EN)
-------------------
%^abc
select h.isChar('%^abc',h.c_spc) from dual;
H.ISCHAR('%^abc',H.C_SPC)
-------------------
%^abc
select h.isChar('%^abc日就月將',h.c_ch) from dual;
H.ISCHAR('%^abc日就月將',H.C_CH)
-------------------
%^abc日就月將
select h.isChar('%^abcわご',h.c_jr) from dual;
H.ISCHAR('%^abcわご',H.C_JR)
-------------------
%^abcわご
select h.isChar('%^abc한글',h.c_kr) from dual;
H.ISCHAR('%^abc한글',H.C_KR)
-------------------
%^abc한글

isChar (table)

This function get total number of each language character.

At this version only support English, Chinese, Korean, Japanese, NUMBER, Special Character.

select keydata, valdata from table (h.isChar('1abc한글%~123abcわご日就月將@@@')) order by valdata desc 
KEYDATA VALDATA
-------------------
ENGLISH 6 SPECIAL 5 NUMBER 4 CHINESE 4 KOREAN 2 JAPANESE 2