Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle SUBSTR Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: SUBSTR

Definition:
The Oracle SUBSTR (SUBSTRING) function lets you extract a portion of a string (a 'substring') from a string of characters.

Example Syntax:

SUBSTR(source_string, starting_position, [ length ]);


The source_string is the string that the substring will be taken from.

The starting_position is the position in the source_string where you want to start extracting characters. The first position in the source_string is always '1', NOT '0', as in many other languages.
  • If the starting_position is 0, then SUBSTR treats start_position as 1 (ie: the first position in the string).

  • If the starting_position is a positive number, then SUBSTR starts from the beginning of the string.

  • If the starting_position is a negative number, then SUBSTR starts from the end of the source_string and counts backwards.

The length parameter is optional, and specifies the number of characters to extract. If the length parameter is not used then SUBSTR will return everything from the starting_position to the end of the string.
  • If length is a negative number, then SUBSTR will return a NULL value.

Use this sample string with indexed character positions to refer to the examples below.

Example Usage:

SUBSTR('Dinner starts in one hour.', 8, 6)    - will return 'starts'
SUBSTR('Dinner starts in one hour.', 8) - will return 'starts in one hour.'
SUBSTR('Dinner starts in one hour.', 1, 6) - will return 'Dinner'
SUBSTR('Dinner starts in one hour.', 0, 6) - will return 'Dinner'
SUBSTR('Dinner starts in one hour.', -4, 3) - will return 'our'
SUBSTR('Dinner starts in one hour.', -9, 3) - will return 'one'
SUBSTR('Dinner starts in one hour.', -9, 2) - will return 'on'



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 70 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?