My Oracle Support Banner

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

Last updated on JANUARY 31, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.2 [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.
 


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

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
Goal
Solution

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