Meta Characters in Oracle

Meta characters are the characters in a programming language that are interpreted by the programming language to have specific meanings within the language itself.

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 ” > “.

 

html--tag.png

The above example shows the meta characters used for HTML and the below example shows how meta characters are used in XML :

xml-tag.png

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

 

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

 

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> 

 

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> 

 

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.

 

Thank you for reading my blog , the reference is taken from one of my article that was published by Experts-Exchange. Please feel free to leave me some feedback or to suggest any future topics.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s