SUBSTR Function in Oracle SQL – PLSQL

The SUBSTR Function in Oracle SQL / PLSQL is used to extract a string from within another string.

Syntax for the using the SUBSTR Function in Oracle SQL / PLSQL is:

SELECT SUBSTR(string_1, start_position [,length])
FROM table_name;

  • string_1 is the string from which the substring is to be extracted.
  • start_position is the position in string_1 from where the extraction will start, the first position is always 1.
  • length is optional parameter, it describes the number of characters to be extracted, if omitted, the SUBSTR function returns the string_1 from the start_position provided.

Also,

  • If start_position is 0, then start_position is treated as 1.
  • If start_position is any positive number then SUBSTR function starts counting from the beginning of the string.
  • If start_position is any negative number then SUBSTR function starts counting from the end of the string, counting backwards.
  • If length is passed as a negative number then SUBSTR function returns NULL.

Examples:
Using SUBSTR Function in Oracle SQL / PLSQL SELECT statement:

[sourcecode language=”sql”]
SELECT SUBSTR(‘Tech Honey’,1,4)
FROM dual;
[/sourcecode]

Will return ‘Tech’

[sourcecode language=”sql”]
SELECT SUBSTR(‘Tech Honey’,1,6)
FROM dual;
[/sourcecode]

Will return ‘Tech H’

[sourcecode language=”sql”]
SELECT SUBSTR(‘Tech Honey’,3,6)
FROM dual;
[/sourcecode]

Will return ‘ch Hon’

[sourcecode language=”sql”]
SELECT SUBSTR(‘Tech Honey’,0,6)
FROM dual;
[/sourcecode]

Will return ‘Tech H’

[sourcecode language=”sql”]
SELECT SUBSTR(‘Tech Honey’,-4,6)
FROM dual;
[/sourcecode]

Will return ‘oney’

[sourcecode language=”sql”]
SELECT SUBSTR(‘Tech Honey’,-4,-6)
FROM dual;
[/sourcecode]

Will return NULL


Leave a Reply

Your email address will not be published. Required fields are marked *