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.
This is a simple string function that will combine two strings. Syntax:
concat(string1, string2)
Supposing you are asked to print the Doctors first and last name in the DoctorsList table and you need to add “MD” at the end of the lastname to signify Doctor of Medicine, this is the SQL query you would use:
SELECT "FirstName" FIRSTNAME,concat("LastName",', MD') LASTNAME from "DoctorsList";
This is the output:
FIRSTNAME LASTNAME Dr. Meri Keller, MD Dr. John Know, MD Dr. Arturo Ortiz, MD Dr. Samantha Fox, MD Dr. Peter East, MD
Oracle translate() Function
A simple way of replacing any sequence of characters is using the translate function. You do not need to deal with the number of characters in your given string. All you need to define is string to replace and your replacement string. Syntax:
For example, supposing you are asked to print an output of Doctors First and Last name but you are asked to remove the Doctors initial and replace it with “Mr.”
This is the SQL query:
SELECT translate("FirstName",'Dr.','Mr.') FIRSTNAME,"LastName" LASTNAME from "DoctorsList";
This is output of this query:
FIRSTNAME LASTNAME Mr. Meri Keller Mr. John Know Mr. Arturo Ortiz
Oracle length() Function
This is a very simple function to return the number of characters of a given string. Syntax:
length(givenstring)
You can use this function along with other Oracle string functions that needs the string length data such as the substr and lpad function.
For example in the substr example to remove the “Dr.” initial from the first name:
SELECT substr("FirstName",5,20) FIRSTNAME,"LastName" LASTNAME from "DoctorsList";
The length of the substr which is 20 is estimated only and does not depend on the FirstName character length. The downside of the above query is that if the first name is more than 20 characters, then it won't be properly displayed on the report because it will be truncated.
The solution is to use the length string function that count the number of characters for the string. The appropriate query would be:
SELECT substr("FirstName",5,length("FirstName")-3) FIRSTNAME,"LastName" LASTNAME from "DoctorsList";
This is the output:
FIRSTNAME LASTNAME Meri Keller John Know Arturo Ortiz Samantha Fox Peter East
Using the above technique, regardless of the FirstName number of characters it would still display correctly since substr length does not depend on single figure but varies depending on the FirstName length.
