ANSI_SUBSTRING Option [TSQL]

ON, OFF

ON

Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. 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.

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