Meta Characters in Oracle

Meta characters are normally simple characters. For example we can take HTML or XML where we use less than or greater than symbols i.e. " < " and " > ". 

The above example shows the meta characters used for HTML and the below example shows how meta characters are used in XML : In Oracle SQL we use the percentage symbol and underscore to make use of like condition for pattern matching. Normally "%" finds zero or more characters inside a string and "_" finds a single character only, in a specific position within a string. In Oracle we use POSIX (Portable Operating System Interface for Unix) standard meta characters for regular expressions. We can see the POSIX table in Oracle under "Table D-2 POSIX and Multilingual Operator Relationships". For example an asterisk operator in Oracle matches the preceding pattern element 0 or more times. 

In Oracle we are also allowed to use meta characters that are used in PERL but these are not a part of POSIX standards as they are platform independent. These characters are also known as Perl influenced operators. From Oracle documentation we can see the detail list of the operators or meta characters at "Perl-influenced Extensions in Oracle Regular Expressions". 

To see how the meta characters are used in Oracle we look at how they are used in regular expression functions. Here are some example of regular expressions :

• REGEXP_INSTR : Finds the position of the string within a string
• REGEXP_SUBSTR : This helps to find the specific string within a string 
• REGEXP_REPLACE : This helps in replacing a string within a string 
• REGEXP_COUNT : This function helps in getting the count of the occurrence of a string within a string
• REGEXP_LIKE : This is very similar to LIKE operator but in regular expression we can use this more significantly for patten matching. 

To demonstrate let us take "REGEXP_INSTR" to check how we can use the meta characters. I am using a table called as "STUDENT" which has as below: 
 

Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as STUDENT
                      
                      SQL> select FULLNAME,PHONE from STUDENTS;
                      
                      FULLNAME                                           PHONE
                      -------------------------------------------------- --------------------
                      MICHEAL JORDON                                     9873737334
                      TIM SLIM                                           9833334334
                      HUNTER JASON                                       34344343
                      JAMES GOODWILL                                     9989898998
                      KENNY PETERSON                                     9983373333
                      GLEN JHONSON                                       9898398985
                      BATES KATHY                                        234423232
                      
                      7 rows selected

Select all Open in new window

Let us find the record that is the first occurrence within the FULLNAME column of anything with capital letter "B". To write proper SQL we have to first identify the record using the pattern match and then we can pull the record. The SQL below is used to identify the record first :
 

SQL> SELECT FULLNAME,PHONE,  REGEXP_INSTR(FULLNAME, 'B(/*)') AS VAL FROM STUDENTS ;
                      
                      FULLNAME                                           PHONE                       VAL
                      -------------------------------------------------- -------------------- ----------
                      MICHEAL JORDON                                     9873737334                    0
                      TIM SLIM                                           9833334334                    0
                      HUNTER JASON                                       34344343                      0
                      JAMES GOODWILL                                     9989898998                    0
                      KENNY PETERSON                                     9983373333                    0
                      GLEN JHONSON                                       9898398985                    0
                      BATES KATHY                                        234423232                     1
                      
                      7 rows selected

Select all Open in new window

So from the above we can see that the VAL column helps to identify the record having the full name starting with the letter "B" in capitals. 

To bring the exact record we can write something like this: 
 

Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as STUDENT
                      
                      SQL> 
                      SQL> SELECT FULLNAME, PHONE
                        2    FROM (SELECT FULLNAME, PHONE, REGEXP_INSTR(FULLNAME, 'B(/*)') AS VAL
                        3            FROM STUDENTS)
                        4   WHERE VAL > 0;
                      
                      FULLNAME                                           PHONE
                      -------------------------------------------------- --------------------
                      BATES KATHY                                        234423232
                      
                      SQL> 

Select all Open in new window

 The above SQL is very similar to the following, that uses LIKE operator:
 

SQL> SELECT FULLNAME, PHONE FROM STUDENTS where FULLNAME like '%B%';
                      
                      FULLNAME                                           PHONE
                      -------------------------------------------------- --------------------
                      BATES KATHY                                        234423232
                      
                      SQL> 

Select all Open in new window

This shows the meta characters in Oracle and how POSIX is used to retrieve the records with various ways.

Thank you for reading my article. Please feel free to leave me some feedback or to suggest any future topics. 
Please 'Vote this article as helpful' if you liked on the bug green button at the bottom of this article.

Looking forward to hear from you - Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )