SUBSTR

The SUBSTR function extracts and returns a portion of a character string or bit string expression, starting at the specified character or bit position. You can specify the number of characters or bits you want returned, or use the default length, which is to extract from the specified starting position to the end of the string.

Syntax

SUBSTR({ CharacterExpression },
		StartPosition [, LengthOfSubstring ] )

CharacterExpression

A CHAR, VARCHAR, or LONG VARCHAR data type or any built-in type that is implicitly converted to a string (except a bit expression).

StartPosition

An integer expression; for character expressions, this is the starting character position of the returned substring. For bit expressions, this is the bit position of the returned substring.

The first character or bit has a StartPosition of 1. If you specify 0, Splice Machine assumes that you mean 1.

If the StartPosition is positive, it refers to the position from the start of the source expression (counting the first character as 1) to the beginning of the substring you want extracted. The StartPosition value cannot be a negative number.

LengthOfSubstring

An optional integer expression that specifies the length of the extracted substring; for character expressions, this is number of characters to return. For bit expressions, this is the number of bits to return.

If this value is not specified, then SUBSTR extracts a substring of the expression from the StartPosition to the end of the source expression.

If LengthOfString is specified, SUBSTR returns a VARCHAR or VARBIT of length LengthOfString starting at the StartPosition.

The SUBSTR function returns an error if you specify a negative number for the parameter LengthOfString.

Results

For character string expressions, the result type is a VARCHAR value.

The length of the result is the maximum length of the source type.

Examples

The following query extracts the first four characters of each player’s name, and then extracts the remaining characters:

splice> SELECT DisplayName,
   SUBSTR(DisplayName, 1, 4) "1to4",
   SUBSTR(DisplayName, 4) "5ToEnd"
   FROM Players
   WHERE ID < 11;
DISPLAYNAME             |1To4|5ToEnd
------------------------------------------------------
Buddy Painter           |Budd|dy Painter
Billy Bopper            |Bill|ly Bopper
John Purser             |John|n Purser
Bob Cranker             |Bob | Cranker
Mitch Duffer            |Mitc|ch Duffer
Norman Aikman           |Norm|man Aikman
Alex Paramour           |Alex|x Paramour
Harry Pennello          |Harr|ry Pennello
Greg Brown              |Greg|g Brown
Jason Minman            |Jaso|on Minman

10 rows selected

See Also