4.9.2.61 PREFIX function (extension)

Syntax

PREFIX(char_expression, start_position, char_expression)

Description

The scalar function PREFIX returns the substring of a character string starting from the position specified by start position, and ending before the specified character.

Arguments

char_expression

An expression that evaluates to a character string, typically a character-string literal or column name. If the expression evaluates to null, PREFIX returns null.

start_position

An expression that evaluates to an integer value. PREFIX searches the string specified in the first argument starting at that position. A value of 1 indicates the first character of the string.

char_expression

An expression that evaluates to a single character. PREFIX returns the substring that ends before that character. If PREFIX does not find the character, it returns the substring beginning with start_position, to the end of the string. If the expression evaluates to more than one character, PREFIX ignores all but the first character.

Example

SELECT C1, C2, PREFIX(C1, 1, '.') FROM T1;
C1           C2  PREFIX(C1,1,.
--           --  -------------
test.pref    .   test
pref.test    s   pref
2 records selected

SELECT C1, C2, PREFIX(C1, 1, C2) FROM T1;
C1           C2  PREFIX(C1,1,C
--           --  -------------
test.pref    .   test
pref.test    s   pref.te
2 records selected

SELECT C1, C2, PREFIX(C1, 1, 'Q') FROM T1;
C1           C2  PREFIX(C1,1,Q
--           --  -------------
test.pref    .   test.pref
pref.test    s   pref.test
2 records selected