ANSI_SUBSTRING Option [TSQL]

Allowed Values

ON, OFF

Default

ON

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Remarks

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 SAP 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.

Example

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  ==> 'gh'
	// 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  ==> 'abcd'