/*=====
    by: Sharon Tuttle
    created: 2021-12-03
    last modified: 2021-12-03

    Sarah Jimenez asked a very interesting question --
    what DOES happen if you use a position of 0 in the
    SQL substr function?

    (since the descriptions I've seen of the SQL substr
    function note that it uses 1 (!!) as the position 
    for the first character in the string!)

   APPARENT ANSWER: NOTHING BAD HAPPENS !!!!!
   *   see paired queries below, trying with 0 then 1,
       and I don't see any difference in their results!

   *   checking my trusty Sunderraman Oracle text -- it SHOWS
       examples where it is clear that 1 works as the position
       of the first character, but doesn't state that it is
       required;

   *   looking up Oracle online docs -- 
       found the following at:
       https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm
       
       "The SUBSTR functions return a portion of char, beginning at 
       character position....
       ....
       *   If position is 0, then it is treated as 1."

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

       ...so the position of the 1st character of a string is
       considered to be 1, but the Oracle SQL substr function treats a
       position of 0, if given, as a 1! 

   *   DOES the SQL Standard specify...?

       I don't know if the SQL substr function is part of the SQL
       standard, or if the position of characters in a string are
       part of that;
       ...it looks like you may have to buy that to look through it?! 
       (IF I am reading:
       https://www.iso.org/standard/63555.html
       ...correctly, which I might not be!)

   SO: it *looks* like, at least in terms of the Oracle SQL substr
   function, that you won't get an error for a position in a string
   of 0!

=====*/

spool position-experiment-out.txt

prompt =====
prompt experiment 1: calling substr with 0, then 1
prompt for a substring of length 1
prompt =====

select '[' || substr(empl_last_name, 0, 1) || ']' pos_0
from   empl;

select '[' || substr(empl_last_name, 1, 1) || ']' pos_1
from   empl;

prompt =====
prompt experiment 2: calling substr with 0, then 1
prompt for a substring of length 5
prompt =====

select '[' || substr(empl_last_name, 0, 5) || ']' pos_0
from   empl;

select '[' || substr(empl_last_name, 1, 5) || ']' pos_1
from   empl;

prompt =====
prompt experiment 3: calling substr with 0, then 1
prompt using the 2-argument version of substr
prompt =====

select '[' || substr(empl_last_name, 0) || ']' pos_0
from   empl;

select '[' || substr(empl_last_name, 1) || ']' pos_1
from   empl;

spool off