stripout numeric data from the column value in oracle

I want stripout numeric data from the column value

ex :: Column name = abc
abc having the values like
INV242436,
APPY246578,
PO141423,
FSO897623,
APAC126756
I want like this..
INV,
APPY,
PO,
FSO,
APAC
PRAVEEN TAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please use like this ..

We have use like below

SELECT regexp_replace(abc,'\d','') abc from YourTableName

Sample Tested Code

SELECT * FROM
(
SELECT regexp_replace('INV242436','\d','') a from dual UNION ALL
SELECT regexp_replace('APPY246578','\d','') a from dual UNION ALL
SELECT regexp_replace('PO141423','\d','') a from dual UNION ALL
SELECT regexp_replace('FSO897623','\d','') a  from dual UNION ALL
SELECT regexp_replace('APAC126756','\d','') a from dual
)o

Open in new window


OUTPUT

 	A
1	INV
2	APPY
3	PO
4	FSO
5	APAC

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Or below ones..

SELECT * FROM
(
SELECT regexp_replace('INV242436','[0-9]','') a from dual UNION ALL
SELECT regexp_replace('APPY246578','[0-9]','') a from dual UNION ALL
SELECT regexp_replace('PO141423','[0-9]','') a from dual UNION ALL
SELECT regexp_replace('FSO897623','[0-9]','') a  from dual UNION ALL
SELECT regexp_replace('APAC126756','[0-9]','') a from dual
)o

Open in new window


or
SELECT * FROM
(
SELECT regexp_replace('INV242436','[[:digit:]]','') a from dual UNION ALL
SELECT regexp_replace('APPY246578','[[:digit:]]','') a from dual UNION ALL
SELECT regexp_replace('PO141423','[[:digit:]]','') a from dual UNION ALL
SELECT regexp_replace('FSO897623','[[:digit:]]','') a  from dual UNION ALL
SELECT regexp_replace('APAC126756','[[:digit:]]','') a from dual
)o

Open in new window


Output

 	A
1	INV
2	APPY
3	PO
4	FSO
5	APAC

Open in new window

0
 
PRAVEEN TAuthor Commented:
thank you .. working fine
0
 
Pawan KumarDatabase ExpertCommented:
We can also use Translate function for this kind of requirement.

WITH CTE AS
(
	select 'INV242436' a from dual union all
	select 'APPY246578' from dual union all
	select 'PO141423' from dual union all
	select 'FSO897623' from dual union all
	select 'APAC126756' from dual
)
SELECT translate(a, 'a0123456789', 'a') a FROM CTE

Open in new window


OUTPUT

 	A
1	INV
2	APPY
3	PO
4	FSO
5	APAC

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Welcome. Happy to help. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.