×
☰ See All Chapters

Oracle SUBSTR Function

Oracle SUBSTR function extracts parts of a string, beginning at the character at a specified position, and returns a specified number of characters.

Oracle SUBSTR Function Syntax

SUBSTR(CHARACTERSET, STARTING_POSITION, LENGTH)

There are normally three arguments with the SUBSTR function:

CHARACTERSET: Source string from which substring to be extracted. This can be an expression or a direct value or a value from a column of any type.

STARTING_POSITION:  The position where to start the extraction. First character is at index 0. If start is positive and greater than, or equal, to the length of the string, substr() returns null.

LENGTH: Optional. The number of characters to extract. If omitted, it extracts the rest of the string

Oracle SUBSTR Function Example

Creating table for demonstrating SUBSTR Function

CREATE TABLE NAME

(

  FNAME  VARCHAR(10 )    NOT NULL,

  LNAME  VARCHAR(10 )

);

Insert into NAME (FNAME, LNAME) Values ('ADI', 'TEMP');

Insert into NAME (FNAME, LNAME) Values ('NAVEEN', 'SHETTY');

Insert into NAME (FNAME, LNAME) Values ('ARJUN', 'SHETTY');

Insert into NAME (FNAME, LNAME) Values ('HARISH', 'GOWDA');

Insert into NAME (FNAME, LNAME) Values ('HARI', 'PRASAD');

Insert into NAME (FNAME, LNAME) Values ('ARJUN', 'SHETTY');

Insert into NAME (FNAME, LNAME) Values ('KIRAN', 'KUMAR');

COMMIT;

Example 1

SELECT  FNAME, SUBSTR(LNAME,2,3)

FROM NAME

oracle-substr-function-0

Example 2

SELECT  FNAME, SUBSTR('PRASAD',2,3)

FROM NAME

oracle-substr-function-1

All Chapters
Author