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

Example

--character set is euc-kr 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 16th position on double byte charset

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

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

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

  '가나'