The MID function extracts a string with the length of substring_length from a position within the 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. substring_length cannot be omitted. If a negative value is specified, the function considers this as 0 and returns an empty string.
The MID function is working like the SUBSTR Function, but there are differences in that it cannot be used for bit strings, that the substring_length argument must be specified, and that it returns an empty string if a negative number is specified for substring_length.
string :
• character string
• NULL
position :
• integer
• NULL
substring_length :
• integer
• NULL
CREATE TABLE mid_tbl(a VARCHAR);
INSERT INTO mid_tbl VALUES('12345abcdeabcde');
--it returns empty string when substring_length is 0
SELECT MID(a, 6, 0), SUBSTR(a, 6, 0), SUBSTRING(a, 6, 0) FROM mid_tbl;
mid(a, 6, 0) substr(a, 6, 0) substring(a from 6 for 0)
==================================================================
'' '' ''
--it returns 4-length substrings counting from the 6th position
SELECT MID(a, 6, 4), SUBSTR(a, 6, 4), SUBSTRING(a, 6, 4) FROM mid_tbl;
mid(a, 6, 4) substr(a, 6, 4) substring(a from 6 for 4)
==================================================================
'abcd' 'abcd' 'abcd'
--it returns a empty string when substring_length < 0
SELECT MID(a, 6, -4), SUBSTR(a, 6, -4), SUBSTRING(a, 6, -4) FROM mid_tbl;
mid(a, 6, -4) substr(a, 6, -4) substring(a from 6 for -4)
==================================================================
'' NULL 'abcdeabcde'
--it returns 4-length substrings at 6th position counting backward from the end
SELECT MID(a, -6, 4), SUBSTR(a, -6, 4), SUBSTRING(a, -6, 4) FROM mid_tbl;
mid(a, -6, 4) substr(a, -6, 4) substring(a from -6 for 4)
==================================================================
'eabc' 'eabc' '1234'