PL/SQL – Strings

What is a String?

A string, also referred to as character data, is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all.

There are three kinds of strings in PL/SQL:

Fixed-length strings – The string is right-padded with spaces to the length specified in the declaration. In such strings, programmers specify the length while declaring the string

Variable-length strings – A maximum length for the string is specified (and it must be no greater than 32,767), but no padding takes place.

Character large objects (CLOBs) – CLOBs are variable-length strings that can be up to 128 terabytes.

Strings can be literals or variables. A string literal is enclosed within quotation marks.

'This is a string literal'

If you need to embed a single quote inside a string literal, you need to type two single quotes right next to one another.

'This isn''t a date'

You can also use the “q” character to indicate an alternative terminating character for the literal.

q'[This isn't a date]'

A string variable is an identifier declared with a string datatype and then assigned a value (which could be a literal or an expression).

Declaring String Variables

Oracle Database offers numerous string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an “N” are “national character set” datatypes, which means they are used to store Unicode character data.

If you need to declare a variable-length string, you must provide the maximum length of that string. The following code declares a variable, using the VARCHAR2 datatype, that will hold a application name, which cannot have more than 50 characters:

application_name VARCHAR2(50);

You must provide the maximum length; if you leave it out, Oracle Database raises a compile error, as shown below.

application_name VARCHAR2;
application_name := 'Learn PL/SQL';

Error report -
ORA-06550: line 2, column 20:
PLS-00215: String length constraints must be in range (1 .. 32767)
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

To declare a fixed-length string, use the CHAR datatype.

yes_or_no CHAR(1) := 'Y';

With CHAR (unlike with VARCHAR2) you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length of 1. In other words, the two declarations below are identical:

 yes_or_no1 CHAR(1) := 'Y';
 yes_or_no2 CHAR := 'Y';

If you declare a CHAR variable with a length greater than 1, Oracle Database automatically pads whatever value you assign to that variable with spaces to the maximum length specified.

To declare a character large object, use the CLOB datatype and you don’t need to specify maximum length. The length is determined automatically by Oracle Database based on the database block size.

large_content CLOB;

Here are some guidelines.

  • If your string might contain more than 32,767 characters, use the CLOB (or NCLOB) datatype.
  • If the value assigned to a string always has a fixed length, use CHAR (or NCHAR).
  • Otherwise (most of the time), use the VARCHAR2 datatype (or NVACHAR2, when working with Unicode data).

Using Built-in Functions with Strings.

S.No Function & Purpose
1 ASCII(x);

Returns the ASCII value of the character x.

2 CHR(x);

Returns the character with the ASCII value of x.

3 CONCAT(x, y);

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


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

5 INSTR(x, find_string [, start] [, occurrence]);

Searches for find_string in x and returns the position at which it occurs.

6 INSTRB(x);

Returns the location of a string within another string, but returns the value in bytes.

7 LENGTH(x);

Returns the number of characters in x.


Returns the length of a character string in bytes for single byte character set.

9 LOWER(x);

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

10 LPAD(x, width [, pad_string]) ;

Pads x with spaces to the left, to bring the total length of the string up to width characters.

11 LTRIM(x [, trim_string]);

Trims characters from the left of x.

12 NANVL(x, value);

Returns value if x matches the NaN special value (not a number), otherwise x is returned.


Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.

14 NLS_LOWER(x) ;

Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.

15 NLS_UPPER(x);

Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.

16 NLSSORT(x);

Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.

17 NVL(x, value);

Returns value if x is null; otherwise, x is returned.

18 NVL2(x, value1, value2);

Returns value1 if x is not null; if x is null, value2 is returned.

19 REPLACE(x, search_string, replace_string);

Searches x for search_string and replaces it with replace_string.

20 RPAD(x, width [, pad_string]);

Pads x to the right.

21 RTRIM(x [, trim_string]);

Trims x from the right.

22 SOUNDEX(x) ;

Returns a string containing the phonetic representation of x.

23 SUBSTR(x, start [, length]);

Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.

24 SUBSTRB(x);

Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems.

25 TRIM([trim_char FROM) x);

Trims characters from the left and right of x.

26 UPPER(x);

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

message varchar2(30) := 'greetings from PL/SQL'; 

/* find the location of the first "e" */ 
dbms_output.put_line ( INSTR (message, 'e'));

/* retrieve the first character in the string */ 
dbms_output.put_line ( SUBSTR (message, 1, 1)); 
/* retrieve the last character in the string */ 
dbms_output.put_line ( SUBSTR (message, -1, 1)); 
/* retrieve five characters, 
starting from the seventh position. */ 
dbms_output.put_line ( SUBSTR (message, 7, 5)); 
/* retrieve the remainder of the string, 
starting from the second position. */ 
dbms_output.put_line ( SUBSTR (message, 2)); 
greetings from pl/sql
Greetings From Pl/Sql
ngs f
reetings from PL/SQL

PL/SQL procedure successfully completed.

Results :




greetings from pl/sql


Greetings From Pl/Sql

dbms_output.put_line ( INSTR (message, 'e')); 




dbms_output.put_line ( SUBSTR (message, 1, 1)); 


dbms_output.put_line ( SUBSTR (message, -1, 1)); 


dbms_output.put_line ( SUBSTR (message, 7, 5)); 

ngs f

dbms_output.put_line ( SUBSTR (message, 2));


Post your Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s