SUBSTR

We have all used SUBSTR at some time in our varied programming careers and can be fairly justified that we get what we need from the function.

As in previous articles there is more to this simple function than meets the eye.

The basic format of all SUBSTR type functions is that they take an input string, a start position and a number of characters and return that portion of the input string from the start position to the length of the number of characters requested.

for example:

SELECT SUBSTR ('1234567890', 5, 3)
  FROM DUAL;


SUB
---
567

1 row selected.

starts at position 5 and returns the next three characters from the input string.

There are several overloaded variations on this.

To return the remaining string from position 5 to the end you would write something like

SELECT SUBSTR ('1234567890', 5)
  FROM DUAL;


SUBSTR
------
567890

1 row selected.

The last argument has been omitted, and the assumption, on the part of the programmer, is that the number of characters requested is to the end of the string.

So now what? For 99% of what we want to do this is all we want and need to know. With a little manipulation we can extract portions of any string if we know its length and have a starting position.

What we should be doing is either, having a quick dip into the manual to see if that is all there is to SUBSTR, or trying to break it by throwing assorted combinations of inputs at it. So here goes:

SELECT SUBSTR (1234567890, 5, 2)
  FROM DUAL;


SU
--
56

1 row selected.

Well it seems to handle implicit conversions of numbers into strings, how about trying some negative number inputs? Lets see if we can get the number of characters before a start postion.

SELECT SUBSTR ('1234567890', 5 , -2)
  FROM DUAL;






1 row selected.

Not quite broken, but no error thrown. So that didn't work out too well, lets now try reversing the whole idea and seeing if a negative starting position has any effect?

SELECT SUBSTR ('1234567890', -4 , 2)
  FROM DUAL;


SU
--
78

1 row selected.

So from this example we can go 4 positions back from the end of the input string to start and return the 2 characters from there onwards. Omitting the number of characters returns the remainder of the input string. This means that I can now find the last x number of characters of any length input string without knowing it's length.

As an exercise to you, try repeating the last SQL statement but replace -4 with -14.