Strings in PL/SQL - 2 (Functions with Examples)

In this article, we are going to learn about the strings in PL/SQL and it’s some of the important functions which may help us to manipulate strings in PL/SQL in a better way.
Submitted by Yash Kumar, on October 18, 2017

In the previous article, (Strings and its types in PL/SQL), we have discussed about the strings, its types like fixed length strings, variable length strings and character large objects (CLOBs) with example (PL/SQL statements).

Here, we will learn about some of the important and popular string functions in PL/SQL, these are the functions:

1) integer ASCII( character x)

Returns the character with the ASCII value of x.

Example

declare
ch1 char(1);
ch2 char(1);
i integer;

begin
i:=67;
ch1:=CHR(i);
ch2:=CHR(97);

dbms_output.put_line(ch1);
dbms_output.put_line(ch2);
end;

Output

C
a
Statement processed.
0.00 seconds

2) character CHR(int)

Returns the ASCII value of the character x.

Example

declare
character char(1);
i integer;
j integer;

begin
character:='t';
i:=ASCII('A');
j:=ASCII(character);

dbms_output.put_line(i);
dbms_output.put_line(j);
end;

Output

65
116
Statement processed.
0.02 seconds

3) string concat(string x,string y)

Concatenates the strings x and y and returns the appended string.

Example

declare
ch1 varchar2(10);
ch2 varchar2(10);
ch3 varchar2(20);

begin
ch1:='include';
ch2:='help';
ch3:=concat(ch1,ch2);

dbms_output.put_line('your first string : '||ch1);
dbms_output.put_line('your second string : '||ch2);
dbms_output.put_line('your resulteant:   '||ch3);
end;

Output

your first string : include
your second string : help
your resulteant:   includehelp

Statement processed.
0.00 seconds

4) string INITCAP(string x)

Converts the initial letter of each word in x to uppercase and returns that string.

Example

declare
ch1 varchar2(10);
ch2 varchar2(10);

begin
ch1:='include';
ch2:=INITCAP(ch1);

dbms_output.put_line('your first string : '||ch1);
dbms_output.put_line('your second string : '||ch2);
end;

Output

your first string : include
your second string : Include

Statement processed.
0.00 seconds

5) integer INSTR( string y , substring x)

Searches for specified substring x in y and returns the position at which it occurs. It will return 0 if substring not found.

Example

declare
string varchar2(20);
substring varchar2(10);
i integer;

begin
string:='the man the machine';
substring:='man';
i:=INSTR(string ,substring);

dbms_output.put_line('Your substring is found at position :'||i);
end;

Output

Your substring is found at position :5

Statement processed.
0.00 seconds

6) integer length(string x)

Returns the number of characters in x.

Example

declare
string varchar2(20);
i integer;

begin
string:='the man the machine';
i:=length(string);

dbms_output.put_line('length of your string :'||i);
end;

Output

Your substring is found at position :5

Statement processed.
0.00 seconds

7) string LOWER(string x)

Converts the letters in x to lowercase and returns that string.

8) string UPPER(string x)

Converts the letters in x to lowercase and returns that string.

Example

declare
string1 varchar2(20);
string2 varchar2(20);
string3 varchar2(30);

begin
string1:='the man the machine';
string2:=UPPER(string1);

dbms_output.put_line(string2);

string3:=LOWER('INCLUDE HELP IS  A EXCELLENT ');

dbms_output.put_line(string3);

end;

Output

THE MAN THE MACHINE
include help is  a excellent 

Statement processed.

0.01 seconds

Comments and Discussions!

Load comments ↻

Top MCQs

Top Programs/Examples