SUBSTR (Sub String) Functions are used to retrieve a portion of a given string. The Return value is a string of the same data type as the string.

General Usage: 

The format of SUBSTR function is: SUBSTR (String, Starting position, length).

• String – The string from which we will be retrieving the result.
• Starting position – Position from which we want to retrieve the result.
• Length – Optional – The length of the string from the Starting Position.

Consider the below example:

select substr ('This is a test sentence', 16, 8) from dual;

Here:

• String is – This is a test sentence
• Starting Position – 16
• Length – 8.

Thus, what the sql does is – it calculates the 16th position from the start, and retrieves the next 8 characters in the string. Which gives the result as “sentence”.

The Starting position can be negative too.

select substr ('This is a test sentence', -16, 8) from dual;

In the above scenario, the sql engine sees that the value is negative, and retrieves the 8 characters backward starting from the 16 position in the string. Which gives the result as “ a test ” (that may include the spaces).

Real World Use: In the real world, there may be a requirement where we have a string and we have to derive particular part of a string. In these scenarios, we can use SUBSTR. Consider a real world scenario, like below, a string with Customer Number, Name, Address, city, Zip and Country and delimited by a semi-colon. The string is:

• ‘12345678;Test_Customer_1;1234 Main Street;New York;94949;US‘
• ‘5678;Test_Customer_2;367 Parallel Street;California;38383;US‘

And the requirement is to derive the Customer Name.

The logic is to get the value between the First and Second semi-colons.

Using the Instr function – we get the position of the first semi-colon and the second semi-colon, and using SUBSTR, derive the values between the first and second semi-colon – which gives the Customer name. The below query is thus used:

SELECT SUBSTR (
           '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           + 1,
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 2)
           - INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           - 1)
  FROM DUAL;

Run the query above – Which gives the result as – Test_Customer_1 (It may look a little tricky, but its simple when looked into).