How to Search for a Pattern Like '[A-Z][0-9][0-9]%'
(Doc ID 264655.1)
Last updated on NOVEMBER 03, 2021
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 to 22.214.171.124 [Release 8.1.7 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
"Checked for relevance on 21-Mar-2013"
Generally, the like operator is used to search for simple patterns.
First Character of column data should be 'A' and rest anything ..... LIKE 'A%'
First character 'A' next Number 3 and rest anything ................ LIKE 'A3%'
But what to do if requirement is somewhat like
Select From Table where
First character of Column should be any Alphabet say between 'A' and 'Z'
Second Character of column should be any number sat between 0 and 9
Third can be any Character say '%'
Fourth again Number say between 0 and 9
.................... and so on.
For example, consider table DEPARTMENT with Column NAME having values like
A001ABC- PHYSICS GR#FLOOR
A101ABC- PHYSICS FST#FLOOR
HEAD OF DEPARTMENT
....... AND SO NO.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document