ANSI_SUBSTRING option [TSQL]

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

ON, OFF

ON

When the ANSI_SUBSTRING option is set to ON, the behavior of the SUBSTRING function corresponds to ANSI/ISO SQL/2003 behavior. A negative or zero start offset is treated as if the string were padded on the left with noncharacters, 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 earlier versions of Sybase IQ: 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. Using a start offset of 0 is equivalent to a start offset of 1.

Avoid using nonpositive start offsets or negative lengths with the SUBSTRING function. Where possible, use the LEFT or RIGHT functions instead.

These 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'