Oracle query to find alphanumeric data in column

Sample Data

SELECT ‘3874’ col FROM DUAL
UNION ALL
SELECT ’74’ col FROM DUAL
UNION ALL
SELECT ‘123’ col FROM DUAL
UNION ALL
SELECT ’22FRE’ col FROM DUAL
UNION ALL
SELECT ‘ABCD’ col FROM DUAL
UNION ALL
SELECT ‘AB33FRE’ col FROM DUAL

Query Syntax

SELECT COLUMN_NAME FROM TABLE_NAME WHERE REGEXP_LIKE(COLUMN_NAME,'[[:alpha:]]') AND REGEXP_INSTR(COLUMN_NAME,'[0-9]')>0;
Output
COLUMN_NAME
22FRE
AB33FRE

The Author

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development. I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.

Leave a Reply