Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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

MID Function

Description

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.

Syntax

 

string :

? character string

? NULL

 

position :

? integer

? NULL

 

substring_length :

? integer

? NULL

  • string : Specifies an input character string. If this value is NULL, NULL is returned.
  • position : Specifies the starting position from which the string is to be extracted. The position of the first character is 1. It is considered to be 1 even if it is specified as 0. If the input value is NULL, NULL is returned.
  • substring_lenghth : Specifies the length of the string to be extracted. If 0 or a negative number is specified, an empty string is returned; if NULL is specified, NULL is returned.
Example

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;

 

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

  ''                    ''                    ''

 

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

 

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

  '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;

 

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

  ''                    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;

 

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

  'eabc'                'eabc'                '1234'