asked on

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

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

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

PRAVEEN T

ASKER

thank you .. working fine

Pawan Kumar

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

Pawan Kumar

Welcome. Happy to help. :)