Scribd
Upload
56 views

PL SQL Functions

This document provides examples of built-in functions in Oracle PL/SQL including ASCII, CHR, CONCAT, INITCAP, INSTR, LENGTH, LOWER, UPPER, REPLACE, LPAD, NVL, MOD, and SUBSTR. Each function is demonstrated with a short code example showing how to use the function to manipulate and evaluate strings.

Uploaded by

Rick
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
56 views

PL SQL Functions

This document provides examples of built-in functions in Oracle PL/SQL including ASCII, CHR, CONCAT, INITCAP, INSTR, LENGTH, LOWER, UPPER, REPLACE, LPAD, NVL, MOD, and SUBSTR. Each function is demonstrated with a short code example showing how to use the function to manipulate and evaluate strings.

Uploaded by

Rick
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 14

Oracle PL/SQL

Built In Functions
ASCII function
The ASCII function returns an ASCII encoding number for a character.
The following sample evaluates the first character of the string:

DECLARE
text VARCHAR2(10) := 'Hello';
BEGIN
IF ASCII(SUBSTR(text,1,1)) = 72 THEN
dbms_output.put_line('The first character of the string is [H].');
END IF;
END;
CHR Function
The CHR function returns the binary equivalent character for an ASCII integer in the database
character set or national character set.

The following demonstrates sending a line break in the midst of a string through the standard
out procedure, DBMS_OUTPUT.PUT_LINE. There is a convenient way to force a line break in the
midst of an output string.
DECLARE
text1 VARCHAR2(10) := 'Title';
text2 VARCHAR2(10) := 'Content';
BEGIN
dbms_output.put_line(text1||CHR(10)||text2);
END;
/
It prints
Title
Content
CONCAT Function
The CONCAT function concatenates two strings into one, and it is equivalent to
using the
concatenation operator (||). The prototype is
CONCAT(string1, string2)
DECLARE
text1 VARCHAR2(10) := 'Hello ';
text2 VARCHAR2(10) := 'There!';
BEGIN
dbms_output.put_line(CONCAT(text1,text2));
END;
/
It prints
Hello There
INITCAP Function
The INITCAP function is very handy when you want to convert a string to title
case.

The function takes a string and returns a converted string.


The following demonstrates the function:
DECLARE
text VARCHAR2(12) := 'hello world!';
BEGIN
dbms_output.put_line(INITCAP(text));
END;
/
It prints
Hello World!
INSTR Function
The INSTR function lets you find the position where a substring starts in
a string.

DECLARE
text VARCHAR2(12) := 'Hello World!';
BEGIN
dbms_output.put_line('Start ['||INSTR(text,'World',1)||']');
END;
/
It prints
Start [7]
LENGTH Function
The LENGTH function lets you calculate the length of a string by using
character units.

DECLARE
text VARCHAR2(12) := 'Hello World!';
BEGIN
dbms_output.put_line('Length ['||LENGTH(text)||']');
END;
/
It prints
Length [12]
LOWER Function
The LOWER function lets you convert string to a lowercase character
string:
DECLARE
text VARCHAR2(12) := 'Hello World!';
BEGIN
dbms_output.put_line(LOWER(text));
END;
/
It prints
hello world!
UPPER Function
The UPPER function lets lets you convert string in to a Uppercase
character string:
DECLARE
text VARCHAR2(12) := 'Hello World!';
BEGIN
dbms_output.put_line(UPPER(text));
END;
/
It prints
HELLO WORLD!
REPLACE Function
The REPLACE function lets you search and replace a substring in any CHAR, VARCHAR2, NCHAR,
NVARCHAR2, CLOB, or NCLOB string. It returns the modified string. The prototype is
REPLACE(base_string, search_string, replace_string)
The following demonstrates how to use the function:

DECLARE
base_string VARCHAR2(40) := 'The Republican President said ...';
search_string VARCHAR2(40) := 'Republican';
replace_string VARCHAR2(40) := 'Democratic';
BEGIN
dbms_output.put_line(REPLACE(base_string,search_string,replace_string));
END;
/
It prints
The Democratic President said ...
LPAD Function
The LPAD function lets you add a character one or more times at the
beginning of a string.
DECLARE
output VARCHAR2(10) := 'Wowie';
whitespace VARCHAR2(1) := ' ';
BEGIN
dbms_output.put_line('['||LPAD(output,10,whitespace)||']');
END;
/
It prints
[ Wowie]
NVL Function
The NVL function substitutes a default value when the primary value is null. The
prototype for the
function is NVL(evaluation_parameter, default_substitution_parameter)
The NVL function works well in conditional statements. It removes the possibility
that
comparison values are null. The following program demonstrates a NVL function:
DECLARE
condition BOOLEAN;
BEGIN
IF NOT NVL(condition,FALSE) THEN
dbms_output.put_line('It''s False!');
END IF;
END;
MOD Function
The MOD function lets you find the remainder of a division operation, like the REMAINDER
function.
It returns a zero when there is no remainder and the integer of any remainder when one exists.
The prototype is
MOD(dividend, divisor)
You can use it as follows:

DECLARE
n NUMBER := 16;
m NUMBER := 3;
BEGIN
dbms_output.put_line('Mode ['||MOD(n,m)||']');
END;
/
It prints
Mode [1]
Substr() Function

You might also like