Find a Column Value is Numberic or Alpha Numeric

SQL> create table numalpha ( value varchar2(20));
Table created.

SQL> begin
2 insert into numalpha values (1);
3 insert into numalpha values (2);
4 insert into numalpha values (3);
5 insert into numalpha values ('1a');
6 insert into numalpha values ('2b');
7 insert into numalpha values ('3c');
8 insert into numalpha values ('1a2b');
9 insert into numalpha values ('3c4d');
10 end;
11 /
PL/SQL procedure successfully completed.

Now there are couple to findout non - numeric value in column here I will show you both queries which work with 10g or 11g or previous release.

Example for work in all oracle release(8i to 11g)

1. how to findout non numberic value in column
--Create One function

CREATE OR REPLACE FUNCTION get_valid_str(string_in IN VARCHAR2, valid_chars_in IN VARCHAR2)
RETURN VARCHAR2
IS
tmp VARCHAR2(2000);
BEGIN tmp := TRANSLATE(string_in, chr(0)valid_chars_in, chr(0));
return TRANSLATE(string_in, chr(0)tmp, chr(0));
END;

SQL> select * from numalpha
where value <> get_valid_str(value,'0123456789');
VALUE
--------------------
1a
2b
3c
1a2b
3c4d

2. How to find numberic value in column
--Create one function

create or replace function get_to_number(p_number varchar2)
return number
as
begin
return to_number (p_number);
exception when others then return null;
end;

SQL> select * from numalpha
where get_to_number(value)=value;
VALUE
--------------------
1
2
3

Example for work only in 10g or 11g
Regular Expression

SQL> select
2 value,
3 case when regexp_like(value,'[[:alpha:]]+') then 'A' end
4 case when regexp_like(value,'[[:digit:]]+') then 'N' end AN
5 from numalpha;

VALUE AN
-------------------- --
1 N
2 N
3 N
1a AN
2b AN
3c AN
1a2b AN
3c4d AN

How to replace NON-numeric to '0'

SQL> update numalpha
2 set value = regexp_replace(value,'[[:alpha:]]+',0);
8 rows updated.

SQL> select * from numalpha;
VALUE
--------------------
1
2
3
10
20
30
1020
3040

8 rows selected.

How to remove NON-NUMERIC character in number column

SQL> update numalpha
2 set value = get_valid_str(value,'0123456789')
3 where value <> get_valid_str(value,'0123456789');
5 rows updated.

SQL> select * from numalpha;
VALUE
--------------------
1
2
3
1
2
3
12
34
8 rows selected.

Split Column

Old method.

SQL> select name from test;
NAME
------------------------------
MOHAMMAD TAJ
BABU B
NIVEDITA SHARMA
RASHIDA BASTAWALA
RADO DENISA

SQL> column firstname format a15
SQL> column lastname format a15

SQL> select
2 substr(name,1,instr(name,' ' )-1) as firstname,
3 substr(name, instr(name,' ',-1)+1) as lastname
4 from test;

FIRSTNAME LASTNAME
--------------- ---------------
MOHAMMAD TAJ
BABU B
NIVEDITA SHARMA
RASHIDA BASTAWALA
RADO DENISA

New Method with 10g or 11g.

Regular Expressions
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm#ADFNS1013