On, Off

On

Can be set for an individual connection or for the PUBLIC group. Takes effect immediately.

When the ansi_substring option is set to On, the behavior of the SUBSTRING function corresponds to ANSI/ISO SQL/2008 behavior. A negative or zero start offset is treated as if the string were padded on the left with non-characters, and gives an error if a negative length is provided.

When this option is set to Off, the behavior of the SUBSTRING function is the same as in previous releases of SQL Anywhere: a negative start offset means an offset from the end of the string, and a negative length means the desired substring ends length characters to the left of the starting offset. Also, using a start offset of 0 is equivalent to a start offset of 1.

The setting of this option does not affect the behavior of the BYTE_SUBSTR function. It is recommended that you avoid using non-positive start offsets or negative lengths with the SUBSTRING function. Where possible, use the LEFT or RIGHT functions instead.

• SUBSTRING function [String]
• LEFT function [String]
• RIGHT function [String]
• Compatibility options
• ansi_substring connection property

The following examples show the difference in the values returned by the SUBSTRING function based on the setting of the ansi_substring option.

SUBSTRING( 'abcdefgh',-2,4 );
  ansi_substring = Off ==> 'gh' // substring starts at second-last character
  ansi_substring = On  ==> 'a'  // takes the first 4 characters of 
                                // ???abcdefgh and discards all ?
SUBSTRING( 'abcdefgh',4,-2 );
  ansi_substring = Off ==> 'cd'
  ansi_substring = On  ==> value -2 out of range for destination

SUBSTRING( 'abcdefgh',0,4 );
  ansi_substring = Off ==> 'abcd'
  ansi_substring = On  ==> 'abc'