String-Manipulation Functions

String-manipulation functions perform various operations on strings of characters. The syntax for string-manipulation functions is as follows.

String-Manipulation Functions:
 
                       (1)
|--+-| TRIM Function |-------------------------+----------------|
   |                        (2)                |
   +-| SUBSTRING Function |--------------------+
   |  (3)                       (4)            |
   +--------| SUBSTR Function |----------------+
   |  (3)                        (5)           |
   +--------| REPLACE Function |---------------+
   |  (3)                     (6)              |
   +--------| LPAD Function |------------------+
   |  (3)                     (7)              |
   +--------| RPAD Function |------------------+
   |  (3)                                 (8)  |
   '--------| Case-Conversion Functions |------'
 

1. See page TRIM Function
2. See page SUBSTRING Function
3. Informix extension
4. See page SUBSTR Function
5. See page REPLACE Function
6. See page LPAD Function
7. See page RPAD Function
8. See page Case-Conversion Functions

TRIM Function

The TRIM function removes leading or trailing pad characters from a string.

TRIM Function:
 
|--TRIM--------------------------------------------------------->
 
>--(--+-----------------------------------------+--source_expression)--|
      | .-BOTH-----.                            |
      '-+-TRAILING-+--+-----------------+--FROM-'
        '-LEADING--'  '-trim_expression-'
 

The TRIM function returns a VARCHAR value identical to its character string argument, except that any leading or trailing whitespace characters, if specified, are deleted. If no trim qualifier (LEADING, TRAILING, or BOTH) is specified, BOTH is the default. If no trim_expression is used, a single blank space is assumed. If either the trim_expression or the source_expression evaluates to null, the result of the TRIM function is NULL. The maximum length of the returned string must be 255 bytes or fewer, because the VARCHAR data type supports no more than 255 bytes.

The following example shows some generic uses for the TRIM function:

SELECT TRIM (c1) FROM tab;
SELECT TRIM (TRAILING '#' FROM c1) FROM tab;
SELECT TRIM (LEADING FROM c1) FROM tab;
UPDATE c1='xyz' FROM tab WHERE LENGTH(TRIM(c1))=5;
SELECT c1, TRIM(LEADING '#' FROM TRIM(TRAILING '%' FROM
   '###abc%%%')) FROM tab;

When you use the DESCRIBE statement with a SELECT statement that uses the TRIM function in the projection list, the described character type of the trimmed column depends on the database server that you are using and on the data type of the source_expression. For further information on the GLS aspects of the TRIM function in ESQL/C, see the IBM Informix GLS User's Guide.

Fixed Character Columns

The TRIM function can be specified on fixed-length character columns. If the length of the string is not completely filled, the unused characters are padded with blank space. Figure 5 shows this concept for the column entry '##A2T##', where the column is defined as CHAR(10).

Figure 5. Column Entry in a Fixed-Length Character Column

If you want to trim the sharp sign ( # ) trim_expression from the column, you need to consider the blank padded spaces as well as the actual characters.

For example, if you specify the trim specification BOTH, the result from the trim operation is A2T##, because the TRIM function does not match the blank padded space that follows the string. In this case, the only sharp signs ( # ) trimmed are those that precede the other characters. The SELECT statement is shown, followed by Figure 6, which presents the result.

SELECT TRIM(LEADING '#' FROM col1) FROM taba;

Figure 6. Result of TRIM Operation

This SELECT statement removes all occurrences of the sharp ( # ) sign:

SELECT TRIM(BOTH '#' FROM TRIM(TRAILING ' ' FROM col1)) FROM taba;

SUBSTRING Function

The SUBSTRING function returns a subset of a character string.

SUBSTRING Function:
 
|--SUBSTRING--(--source_string--FROM--start_position--+-------------+--)--|
                                                      '-FOR--length-'
 

Any argument to the SUBSTRING function must be of a built-in data type.

The subset begins at the column position that start_position specifies. The following table shows how the database server determines the starting position of the returned subset based on the input value of the start_position.

In locales for languages with a right-to-left writing direction, such as Arabic, Farsi, or Hebrew, right should replace left in the preceding table.

The size of the subset is specified by length. The length parameter refers to the number of logical characters, rather than to the number of bytes. If you omit the length parameter, or if you specify a length that is greater than the number of characters from start_position to the end of source_string, the SUBSTRING function returns the entire portion of source_ string that begins at start_position. The following example specifies that the subset of the source string that begins in column position 3 and is two characters long should be returned:

SELECT SUBSTRING('ABCDEFG' FROM 3 FOR 2) FROM mytable

The following table shows the output of this SELECT statement.

In the following example, the user specifies a negative start_position for the return subset:

SELECT SUBSTRING('ABCDEFG' FROM -3 FOR 7)
   FROM mytable

The database server starts at the -3 position (four positions before the first character) and counts forward for 7 characters. The following table shows the output of this SELECT statement.

SUBSTR Function

The SUBSTR function has the same purpose as the SUBSTRING function (to return a subset of a source string), but it uses different syntax.

SUBSTR Function:
 
|--SUBSTR--(--source_string--,--start_position--+-----------+--)--|
                                                '-,--length-'
 

Any argument to the SUBSTR function must be of a built-in data type.

The SUBSTR function returns a subset of source_string. The subset begins at the column position that start_position specifies. The following table shows how the database server determines the starting position of the returned subset based on the input value of the start_position.

The length parameter specifies the number of logical characters (not the number of bytes) in the subset. If you omit the length parameter, the SUBSTR function returns the entire portion of source_string that begins at start_position.

If you specify a negative start_position whose absolute value is greater than the number of characters in source_string, or if length is greater than the number of characters from start_position to the end of source_string, SUBSTR returns NULL. (In this case, the behavior of SUBSTR is different from that of the SUBSTRING function, which returns all the characters from start_position to the last character of source_string, rather than returning NULL.)

The next example specifies that the string of characters to be returned begins at a starting position 3 characters before the end of a 7-character source_string. This implies that the starting position is the fifth character of source_string. Because the user does not specify a value for length, the database server returns a string that includes all characters from character-position 5 to the end of source_string.

SELECT SUBSTR('ABCDEFG', -3)
   FROM mytable

The following table shows the output of this SELECT statement.

REPLACE Function

The REPLACE function replaces specified characters within a source string with different characters.

REPLACE Function:
 
|--REPLACE--(--source_string--,--old_string--+---------------+--)--|
                                             '-,--new_string-'
 

Any argument to the REPLACE function must be of a built-in data type.

The REPLACE function returns a copy of source_string in which every occurrence of old_string is replaced by new_string. If you omit the new_string option, every occurrence of old_string is omitted from the return string.

In the following example, the user replaces every occurrence of xz in the source string with t:

SELECT REPLACE('Mighxzy xzime', 'xz', 't')
   FROM mytable

The following table shows the output of this SELECT statement.

LPAD Function

The LPAD function returns a copy of source_string that is left-padded to the total number of characters specified by length.

LPAD Function:
 
|--LPAD--(--source_string--,--length--+---------------+--)------|
                                      '-,--pad_string-'
 

Any argument to the LPAD function must be of a built-in data type.

The pad_string parameter specifies the character or characters to be used for padding the source string. The sequence of pad characters occurs as many times as necessary to make the return string the length specified by length.

The series of pad characters in pad_string is truncated if it is too long to fit into length. If you specify no pad_string, the default value is a single blank.

In the following example, the user specifies that the source string is to be left-padded to a total length of 16 characters. The user also specifies that the pad characters are a series consisting of a hyphen and an underscore ( -_ ).

SELECT LPAD('Here we are', 16, '-_') FROM mytable

The following table shows the output of this SELECT statement.

RPAD Function

The RPAD function returns a copy of source_string that is right-padded to the total number of characters that length specifies.

RPAD Function:
 
|--RPAD--(--source_string--,--length--+---------------+--)------|
                                      '-,--pad_string-'
 

Any argument to the RPAD function must be of a built-in data type.

The pad_string parameter specifies the pad character or characters to be used to pad the source string.

The series of pad characters occurs as many times as necessary to make the return string reach the length that length specifies. The series of pad characters in pad_string is truncated if it is too long to fit into length. If you omit the pad_string parameter, the default value is a single blank space.

In the following example, the user specifies that the source string is to be right-padded to a total length of 18 characters. The user also specifies that the pad characters to be used are a sequence consisting of a question mark and an exclamation point ( ?! )

SELECT RPAD('Where are you', 18, '?!')
   FROM mytable

The following table shows the output of this SELECT statement.