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

BIT_SUBSTR function [Bit array]

Returns a sub-array of a bit array.

BIT_SUBSTR( bit-expression [, start [, length ] ] )

bit-expression    The bit array from which the sub-array is to be extracted.

start    The start position of the sub-array to return. A negative starting position specifies the number of bits from the end of the array instead of the beginning. The first bit in the array is at position 1.

length    The length of the sub-array to return. A positive length specifies that the sub-array ends length bits to the right of the starting position, while a negative length returns, at most, length bits up to, and including, the starting position, from the left of the starting position.

Both start and length can be either positive or negative. Using appropriate combinations of negative and positive numbers, you can get a sub-array from either the beginning or end of the string. Using a negative number for length does not impact the order of the bits returned in the sub-array.

If length is specified, the sub-array is restricted to that length. If start is zero and length is non-negative, a start value of 1 is used. If start is zero and length is negative, a start value of -1 is used.

If length is not specified, selection continues to the end of the array.

The BIT_SUBSTR function is equivalent to, but faster than, the following:

CAST( SUBSTR( CAST( bit-expression AS VARCHAR), 
start [, length ] ) 
AS VARBIT ) 

• SUBSTRING function [String]

SQL/2003    Vendor extension.

The following statement returns 1101:

SELECT BIT_SUBSTR( '001101', 3 );

The following statement returns 10110:

SELECT BIT_SUBSTR( '01011011101111011111', 2, 5 );

The following statement returns 11111:

 SELECT BIT_SUBSTR( '01011011101111011111', -5, 5 );