How to Search for a Pattern Like '[A-Z][0-9][0-9]%' (Doc ID 264655.1)

Last updated on JULY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.2 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.
"Checked for relevance on 21-Mar-2013"


Goal

Generally, the like operator is used to search for simple patterns.
For example:
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
NAME
---------------------------
A001ABC- PHYSICS GR#FLOOR
A101ABC- PHYSICS FST#FLOOR
HEAD OF DEPARTMENT
B104ZZZ- YYYYYYYYYYYY

....... AND SO NO.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms