Home arrow Oracle arrow Page 4 - Oracle String Functions with PL/SQL

Oracle concat(), length(), translate() Functions - Oracle

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.

TABLE OF CONTENTS:
  1. Oracle String Functions with PL/SQL
  2. Oracle instr() and replace() Function
  3. Oracle trim() and lpad() Function
  4. Oracle concat(), length(),
By: Codex-M
Rating: starstarstarstarstar / 3
May 05, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:

translate(givenstring,stringtoreplace,replacementstring)

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.
 



 
 
>>> More Oracle Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: