Oracle– How to Find Numeric/Non-Numeric/Date values From a Table Column?
***Oracle– How to Find Numeric/Non-Numeric/Date values From a Column?
Using TRANSLATE Function
Select TRANSLATE(VALUE, 'x0123456789','x') From TEST_TB Where
TRANSLATE(VALUE, 'x0123456789','x') Is NOT NULL; --String values
Select * From TEST_TB Where
LENGTH(TRANSLATE(VALUE, 'x0123456789','x')) IS NOT NULL And Value is NOT NULL; --String values
Select * From TEST_TB Where
LENGTH(TRANSLATE(VALUE, 'x0123456789','x')) IS NULL And Value is NOT NULL; --Numeric Values
The Expression `LENGTH(TRANSLATE(T.VALUE, 'x0123456789', 'x'))` will return NULL if the field contains only Numeric Characters.
Using MATH
Select '12345'/1 From Dual;
Select 'HELLO'/1 From Dual;
SET SERVEROUTPUT ON
Declare
v_result number;
Cursor cur_val is
Select VALUE From TEST_TB Where VALUE IS NOT NULL;
Begin
For i in cur_val Loop
Begin
Select i.VALUE/1 into v_result From Dual;
Dbms_Output.Put_Line(i.VALUE||' is a Number');
Exception
When others then
Dbms_Output.Put_Line(i.VALUE||' is a NOT Number');
End;
End Loop;
End;
Using Date Function
Select To_Char(To_Date('12345','J'), 'JSP') From Dual;
Select To_Char(To_Date('HELLO','J'), 'JSP') From Dual;
Declare
v_result varchar2(300);
Cursor cur_val is Select VALUE From TEST_TB Where VALUE IS NOT NULL;
Begin
For i in cur_val Loop
Begin
Select To_Char(To_Date(i.VALUE,'J'), 'JSP') into v_result From Dual;
Dbms_Output.Put_Line(i.VALUE||' is a Number');
Exception
When Others Then
Dbms_Output.Put_Line(i.VALUE||' is a NOT Number');
End;
End loop;
End;
Using Regular Expressions
Select * From TEST_TB Where
REGEXP_LIKE(VALUE, '^[[:digit:]]'); --Numeric Values
Fetch Date values
Select * From TEST_TB
Where REGEXP_LIKE (VALUE,'[0-9]{2}[\-]{1}[a-zA-Z]{3}[\-]{1}[0-9]{4}');
#GoLearningPoint