BIT_SUBSTR function [Bit array]

Returns a sub-array of a bit array.

Syntax

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

Parameters

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 )

Standards and compatibility

Example

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 );

Discuss this page in DocCommentXchange.