Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

ansi_substring option

Controls the behavior of the SUBSTRING (SUBSTR) function when negative values are provided for the start or length parameters.

Allowed values

On, Off

Default

On

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No
Remarks

When the ansi_substring option is set to On, the behavior of the SUBSTRING function corresponds to ANSI/ISO SQL Standard 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. Avoid using non-positive start offsets or negative lengths with the SUBSTRING function. Where possible, use the LEFT or RIGHT functions instead.

Example

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'