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.
SUBSTR( string, position [, substring_length])
string :
• character string
• bit string
• NULL
position :
• integer
• NULL
substring_length :
• integer
--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)
======================
'가나'