Why substring results are different when start position is not a positive number?

Question

When I use Sybase system (version 15.0+), query results are different when start position is not a natural number.

If I only SELECT string value, result shows like this.

SELECT  SUBSTR('123456',  1, 3)  AS C1
     ,  SUBSTR('123456',  0, 3)  AS C2
     ,  SUBSTR('123456', -1, 3)  AS C3
     ,  SUBSTR('123456', -2, 3)  AS C4
;

 C1  | C2 | C3 | C4
--------------------
 123 | 12 | 1  |

But, If i use this query by searching specific table, result is different.

SELECT  TOP 1
        SUBSTR('123456',  1, 3)  AS C1
     ,  SUBSTR('123456',  0, 3)  AS C2
     ,  SUBSTR('123456', -1, 3)  AS C3
     ,  SUBSTR('123456', -2, 3)  AS C4
  FROM  TEMP_TABLE
;

 C1  | C2  | C3 | C4
---------------------
 123 | 123 | 6  | 56

I've already searched Sybase manual, but there is not a clear explanation about this situation.

Can somebody explicate about this?

Answer 1

As tinazmu has hinted at in comments, the difference in output can be explained by the ansi_substring configuration setting.

version 15.0+ and substr() (as opposed to substring()) indicates OP is using Sybase SQLAnywhere or Sybase IQ (and not using Sybase ASE nor Sybase Advantage).

The described behavior is documented in both manuals (SQLAnywhere, IQ) and is based on the configuration setting ansi_substring.

NOTE: I'm guessing OP didn't find anything in the manuals because they were looking at the Sybase ASE manuals (which does not support ansi_substring nor does it support the substr() spelling); also, tinazmu has incorrectly linked (in a comment) to an ASE manual

From the SQLAnywhere manual:

Running OP's queries against a SQLAnywhere 17.0.9 database:

1> set option ansi_substring = on
2> go

1> select OptionName, Value
2> from sa_conn_options()
3> where OptionName = 'ansi_substring'
4> go

 OptionName     Value                
 -------------- -----
 ansi_substring On

1> SELECT  SUBSTR('123456',  1, 3)  AS C1
2>      ,  SUBSTR('123456',  0, 3)  AS C2
3>      ,  SUBSTR('123456', -1, 3)  AS C3
4>      ,  SUBSTR('123456', -2, 3)  AS C4
5> go

 C1  C2 C3 C4           
 --- -- -- -- 
 123 12 1                         

1> set option ansi_substring = off
2> go

1> select OptionName, Value
2> from sa_conn_options()
3> where OptionName = 'ansi_substring'
4> go

 OptionName     Value                
 -------------- -----
 ansi_substring Off

1> SELECT  SUBSTR('123456',  1, 3)  AS C1
2>      ,  SUBSTR('123456',  0, 3)  AS C2
3>      ,  SUBSTR('123456', -1, 3)  AS C3
4>      ,  SUBSTR('123456', -2, 3)  AS C4
5> go

 C1  C2  C3 C4           
 --- --- -- -- 
 123 123 6  56 

NOTE: while OP has shown different results from running two different queries, the difference in outputs has nothing to do with the actual queries and everything to do with the ansi_substring setting; for demonstration purposes I've opted to use OP's first query for both tests; results are the same if I use OP's second query (TOP 1 / FROM TEMP_TABLE)

At this point I'm guessing OP's two queries are being run under different ansi_substring settings (eg, using different front-end/query-tools with different default settings for ansi_substring).

Answer 2

1. The first sample from the question and the results has the following logic:

• string '123456' when used as parameter in Substring() function with starting point -1 and number of caharcters to be returned is 3 results as '12'.
• The reason is a fact that Substring function considers first character in the string as position 1. When you ask for starting position -1 and want 3 characters in return the function returns positions (-1, 1, 2) where first one (position -1) doesn't exist and the rest (positions 1 and 2) are '12'.
• The same for starting position -2 and length of 3 - positions -2 and -1 don't exist and the third is 1
• regarding 0 as a starting position - it doesn't exist as such at all so when you put 0 as starting position and length of 3 result will be positions 1,2,3 - just the same as with starting position 1

2. The second sample looks like Oracle and acts like Oracle and it is well documented by Oracle.
   NOTE:
   The above is tested with SQL Server using Substring() function - SQL Server doesn't have Substr() function.
   Results are the same using Postgres which suports both Substr() and Substring() functions. Please see the Postgres fiddle here.

Postgres: