stripout numeric data from the column value in oracle

PRAVEEN T
PRAVEEN T used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
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

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Author

Commented:
thank you .. working fine
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Welcome. Happy to help. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial