Home arrow Oracle arrow Oracle String Functions with PL/SQL

Oracle String Functions with PL/SQL

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 example in this tutorial have been tested on:

a.) Ubuntu 10.04 Lucid Lynx OS
b.) Oracle Database 10g Express Edition

The following is a list of string functions we will be discussing in this tutorial:

1.) substr
2.) instr
3.) replace
4.) trim
5.) lpad
6.) concat
7.) translate
8.) length

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:

CREATE TABLE "DoctorsList"
( "ID" NUMBER (6,0),
"FirstName" VARCHAR2(35),
"LastName" VARCHAR2(35),
CONSTRAINT "DoctorsList_PK" PRIMARY KEY ("ID") ENABLE
);

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.



 
 
>>> 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: