There are
different ways to separate Numeric and character values from a Column. We will understand
this with the help below different ways.
Using TRANSLATE
Function
Using MATH
Using Date Function
Using Regular Expressions
Let’s take an
example with the Below Table.
SQL> SELECT NVL(C1,'NA') From T1;
NVL(C1,'NA')
--------------------
10001
NA
67671
63332
asdfg
Hello
abcdefg
NA
OK
NA
NA
NA
zasjhll
NA
12345
18 rows selected.
Using TRANSLATE Function
The SQL TRANSLATE() function replaces a sequence of
characters in a string with another sequence of characters. The function
replaces a single character at a time.
To get the character values
from Table you can use below query.
SQL> SELECT * From T1
Where LENGTH(TRANSLATE(T1.C1,'x0123456789','x')) Is NOT NULL;
C1
--------------------
asdfg
Hello
abcdefg
OK
zasjhll
To get the numeric values
from Table you can use below query.
SQL> SELECT * From T1
Where LENGTH(TRANSLATE(T1.C1,'x0123456789','x')) Is NULL And C1 Is NOT NULL;
C1
--------------------
10001
67671
63332
12345
The Expression `LENGTH(TRANSLATE(T1.C1,
'x0123456789', 'x'))` will return NULL if the field contains only numeric
characters.
Using MATH
SQL> SELECT '12345'/1 From Dual; '12345'/1 ---------- 12345 SQL> SELECT '0012345'/1 From Dual; '0012345'/1 ----------- 12345 SQL> SELECT 'Hello'/1 From Dual; SELECT 'Hello'/1 From Dual * ERROR at line 1: ORA-01722: invalid number
Based
on above exercise you can define a PL/SQL Block/Procedure/Function to separate
numeric and character values.
Let’s
define a Block to display type of values in the Table. Refer screenshot below
and check the output.
Declare
v_result number;
Cursor c is
Select C1 From T1 Where C1 IS NOT NULL;
Begin
For
r in C loop
Begin
Select r.C1/1 into v_result From Dual;
Dbms_Output.Put_Line(r.C1||' is a Number');
Exception
When others then
Dbms_Output.Put_Line(r.C1||' is a NOT Number');
End;
End
loop;
End;
SQL> Declare
2 v_result number;
3 Cursor c is
4 Select C1 From T1 Where C1 IS NOT NULL;
5 Begin
6 For r in C loop
7 Begin
8 Select r.C1/1 into v_result From Dual;
9 Dbms_Output.Put_Line(r.C1||' is a Number');
10 Exception
11 When others then
12 Dbms_Output.Put_Line(r.C1||' is a NOT Number');
13 End;
14 End loop;
15 End;
16 /
10001 is a Number
67671 is a Number
63332 is a Number
asdfg is a NOT Number
Hello is a NOT Number
abcdefg is a NOT Number
OK is a NOT Number
zasjhll is a NOT Number
12345 is a Number
PL/SQL procedure successfully completed.
Using DATE Function
Let’s
understand how Date function can be used in this scenario.
SQL> SELECT TO_CHAR(TO_DATE(009341,'j'),'JSP') "WORDS" From Dual;
WORDS
-------------------------------------
NINE THOUSAND THREE HUNDRED FORTY-ONE
Based
on above exercise you can define a PL/SQL Block/Procedure/Function to separate
numeric and character values.
Let’s
define a Block to display type of values in the Table- Refer screenshot below
and check the output.
Declare
v_result varchar2(300);
Cursor C is Select C1 From T1 Where C1 Is NOT NULL;
Begin
For
r in C loop
Begin
Select To_Char(To_Date(r.C1,'J'), 'JSP') into v_result From Dual;
Dbms_Output.Put_Line(r.C1||' is a Number');
Exception
When value_error then
Dbms_Output.Put_Line(r.C1||' is a NOT Number');
When Others Then
Dbms_Output.Put_Line(sqlerrm);
End;
End
loop;
End;
SQL> Declare
2 v_result varchar2(300);
3 Cursor C is Select C1 From T1 Where C1 Is NOT NULL;
4 Begin
5 For r in C loop
6 Begin
7 Select To_Char(To_Date(r.C1,'J'), 'JSP') into v_result From Dual;
8 Dbms_Output.Put_Line(r.C1||' is a Number');
9 Exception
10 When value_error then
11 Dbms_Output.Put_Line(r.C1||' is a NOT Number');
12 When Others Then
13 Dbms_Output.Put_Line(sqlerrm);
14 End;
15 End loop;
16 End;
17 /
10001 is a Number
67671 is a Number
63332 is a Number
ORA-01858: a non-numeric character was found where
a numeric was expected
ORA-01858: a non-numeric character was found where
a numeric was expected
ORA-01858: a non-numeric character was found where
a numeric was expected
ORA-01858: a non-numeric character was found where
a numeric was expected
ORA-01858: a non-numeric character was found where
a numeric was expected
12345 is a Number
PL/SQL procedure successfully completed.
The VALUE_ERROR handler
didn't catch the ORA-01858 condition
and is why the output shows the SQL error message. You can write User Defined Exception to exclude
this from your output.
Using REGULAR Expressions
The REGEXP_LIKE function
performs exactly the same as the first approach using TRANSLATE
SQL> SELECT C1 From T1
WHERE REGEXP_LIKE(C1, '^[[:digit:]]+$');
C1
--------------------
10001
67671
63332
12345
Other than Numeric and Non-numeric values what if we
want to separate any Date values from column?
Let’s try this- I have inserted few dates into the
same Table.
SQL> SELECT * From T1 Where C1 IS NOT NULL;
C1
--------------------
10001
67671
63332
asdfg
Hello
abcdefg
OK
21-AUG-2016
05-MAR-2016
zasjhll
07-JUL-2016
12345
12 rows selected.
Using REGULAR Expressions
SQL> SELECT * From T1
2 Where REGEXP_LIKE(C1,'[0-9]{2}[\-]{1}[a-zA-Z]{3}[\-]{1}[0-9]{4}');
C1
--------------------
21-AUG-2016
05-MAR-2016
07-JUL-2016
Using DATE Function
Below Function will return 1 if there is Date value in
column C1 otherwise Function will return 0. Based on this value we can get only
Date values from the Table column.
CREATE OR REPLACE FUNCTION GetDate(DT IN
VARCHAR2) RETURN INTEGER IS
D
DATE;
BEGIN
D :=
TO_DATE(DT, 'DD-MON-YYYY');
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
SQL> CREATE OR REPLACE FUNCTION GetDate(DT IN VARCHAR2) RETURN INTEGER IS
2 D DATE;
3 BEGIN
4 D := TO_DATE(DT, 'DD-MON-YYYY');
5 RETURN 1;
6 EXCEPTION WHEN OTHERS THEN
7 RETURN 0;
8 END;
9 /
Function created.
SQL> SELECT C1 From T1 Where GetDate(C1)=1 and C1 IS NOT NULL;
C1
--------------------
21-AUG-2016
05-MAR-2016
07-JUL-2016
So, You can use any of above method as per your requirement.
Get involved
and leave your Comments in the Box Below. The more people get involved, the
more we all benefit. So, leave your thoughts before you leave
the page.
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete