SUBSTR Function

Description

The SUBSTR function extracts a character string with the length of substring_length from a position, position, within character string, string, and then returns it.

Note In the earlier versions of CUBRID, the starting position and string length are calculated in byte unit, not in character unit. Therefore, in a multi-byte character set, you must specify the parameter in consideration of the number of bytes representing a single character.

Syntax

SUBSTR( string, position [, substring_length])

string:

• character string

• bit string

• NULL

position:

• integer

• NULL

substring_length:

• integer

• string: Specifies the input character string. If the input value is NULL, NULL is returned.
• position: Specifies the position from where the string is to be extracted in bytes. Even though the position of the first character is specified as 1 or a negative number, it is considered as 1. If a value greater than the string length or NULL is specified, NULL is returned.
• substring_length: Specifies the length of the string to be extracted in bytes. If this argument is omitted, character strings between the given position, position, and the end of them are extracted. NULL cannot be specified as an argument value of this function. If 0 is specified, an empty string is returned; if a negative value is specified, NULL is returned.

Example

--character set is UTF-8 for Korean characters

--it returns empty string when substring_length is 0

SELECT SUBSTR('12345abcdeabcde',6, 0);

 substr('12345abcdeabcde', 6, 0)

======================

  ''

--it returns 4-length substrings counting from the position

SELECT SUBSTR('12345abcdeabcde', 6, 4), SUBSTR('12345abcdeabcde', -6, 4);

 substr('12345abcdeabcde', 6, 4)   substr('12345abcdeabcde', -6, 4)

============================================

  'abcd'                'eabc'

--it returns substrings counting from the position to the end

SELECT SUBSTR('12345abcdeabcde', 6), SUBSTR('12345abcdeabcde', -6);

 substr('12345abcdeabcde', 6)   substr('12345abcdeabcde', -6)

============================================

  'abcdeabcde'          'eabcde'

-- it returns 4-length substrings counting from 11th position

SELECT SUBSTR ('12345가나다라마가나다라마', 11 , 4);

 substr('12345가나다라마가나다라마', 11 , 4)

======================

  '가나다라'