Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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. If a negative number is specified as a position value, the position is calculated in a reverse direction from the end of the string. If substring_length is omitted, character strings between the given position, position, and the end of the string are extracted, and then returned.

Note that it returns the starting position and the length of character string in bytes, not in characters. 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 euc-kr for Korean characters

 

--it returns empty string when substring_length is 0

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

 

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

  ''

 

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

SELECT 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);

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

  'abcdeabcde'          'eabcde'

 

-- it returns 4-length substrings counting from 16th position on double byte charset

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

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

  '가나'