SUFFIX

Returns the substring of a character string starting after the position specified by start_pos and the second char_expression, to the end of the string.

Syntax

SUFFIX (char_expression , start_pos , char_expression )

Parameters

char_expression

Evaluates to a character string, typically a character‑string literal or column name. If the expression evaluates to NULL, SUFFIX returns NULL.

start_pos

Evaluates to an integer value. SUFFIX 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

Evaluates to a single character. SUFFIX returns the substring that begins with that character. If SUFFIX does not find the character after start_pos, it returns NULL. If the expression evaluates to more than one character, SUFFIX ignores all but the first character.

Notes

Each char_expression and the result can contain multi‑byte characters. The start_pos argument specifies the character position, not a byte position. Character comparisons are case sensitive and are determined by sort weights in the collation table in the database.

Example

This example illustrates two ways to use the SUFFIX function:

SELECT C1, C2, SUFFIX(C1, 6, '.') FROM T1;
C1      C2 SUFFIX(C1,6,.
--      -- ------------- 
test.pref  .       
pref.test  s        
 2 records selected 
  SELECT C1, C2, SUFFIX(C1, 1, C2) FROM T1;
 C1      C2 SUFFIX(C1,1,C
--      -- ------------- 
test.pref  . pref     
pref.test  s  t      
 2 records selected

Compatibility

Progress extension