This is an introductory tutorial of the most commonly used Oracle string functions using PL/SQL. Oracle string functions help you become efficient in retrieving string information from an Oracle database.
For the best learning experience, I recommended that you have Oracle Database 10g Express edition installed on your system so that you can test the examples illustrated in this tutorial.
Oracle substr() Function
One of the most commonly used Oracle string functions is substr. This function is used to extract specific characters from a string. It works similarly to substr in MS Excel. Supposing the given string is: Hello World and you want to extract the word “World”, one method you could use would be the substr function. This is the syntax of this function:
substr(string, starting_position, length)
“Starting position” is always 1 if it starts on the 1st character of the string. “Length” is the number of characters you want to extract from the beginning of “starting_position”. Illustrating the previous example of a given string “Hello World” and you want to extract the word “World”, the following is the substr syntax you would use:
substr("Hello World", 1, 5)
Since starting from the “H” to “O”(the end of word “Hello”) there are 5 characters to extract, so the length is 5. So how do you use this string function in your Oracle SQL queries? Follow the steps below:
1.) In Ubuntu, go to Applications – Oracle Database 10g Express edition – click “Go to Database Homepage”
2.) Login as database administrator.
3.) Click “SQL”.
4.) Click “SQL” Commands.
5.) In the input text box area, you need to create a sample Oracle table. Copy and paste the query below:
6.) Click “Run”.7.) You need to insert sample data; copy and paste the query below then click “Run”:
INSERT ALL INTO "DoctorsList" VALUES ( 1 , 'Dr. Meri' , 'Keller' ) INTO "DoctorsList" VALUES ( 2 , 'Dr. John' , 'Know' ) INTO "DoctorsList" VALUES ( 3 , 'Dr. Arturo' , 'Ortiz' ) INTO "DoctorsList" VALUES ( 4 , 'Dr. Samantha' , 'Fox' ) INTO "DoctorsList" VALUES ( 5 , 'Dr. Peter' , 'East' ) SELECT * FROM dual;
8.) Suppose your boss asks you to print the output of all doctor names from the database, but without the doctor initial “Dr.” in the first name. For example if the name of the doctor is Dr. Meri Keller, the report output should only be “Meri Keller”. This is a sample SQL query using substr you could use to achieve this:
SELECT substr("FirstName",5,20) FIRSTNAME,"LastName" LASTNAME from "DoctorsList";
This is the output of this query:
Try to experiment with the above SQL query by changing some of its syntax and checking the output.