How to Search for a Pattern Like '[A-Z][0-9][0-9]%'
Last updated on JULY 06, 2017
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [Release 8.1.7 to 12.1]
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms