SUBSTR

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:

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.

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:

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

 

Leave a Reply

  • (will not be published)