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

Last updated on MARCH 04, 2025

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.