MID

MySQL

MID(str, pos, len)

• str: The original string from which a substring is to be extracted.
• pos: The starting position where the extraction of a substring should begin. The position starts from 1 in MySQL.
• len: The length of the substring that is to be extracted. This specifies the number of characters from the starting position (pos) to be included in the substring.

Example

SELECT MID('SQL Documentation', 5, 11) AS ExtractString;

Output

+----------------+
| ExtractString  |
+----------------+
| Documentation  |
+----------------+

Explanation

In the example, the MID function is being used to extract a substring from the string ‘SQL Documentation’, starting from the 5th position and the length of the substring is 11 characters. The output, ‘Documentation’, is the extracted substring.

SQL Server

MID( character_expression , start , length )

• character_expression: This is the string of data from which the substrings will be extracted. It can be a constant, variable, or column of either character or binary data.
• start: This is an integer value specifying the initial position of the substring within the character_expression. Count starts at 1, so 1 indicates the first character of the character_expression. If start is less than 1, the function will return an empty string.
• length: This is an integer value representing the length or number of characters to extract. If length is negative, an error will occur. If length exceeds the length of character_expression, all characters from start position to the end of character_expression are returned.

Example

SELECT SUBSTRING('SQL Server', 2, 3) AS ExtractString;

Output

ExtractString
-------------
QL

Explanation

The SUBSTRING function in SQL Server is used to extract a portion of the string. In the example, it’s starting extraction from the 2nd position and getting the next 3 characters, thus, resulting in ‘QL ’.

Oracle

MID(text, start_position, length)

• text: The original string from which a substring will be derived. This can either be a column name that holds string values or a string literal.
• start_position: Numeric position in the original string where the substring should start. It is an integer value with 1 denoting first character of the given ‘text’. The expression for this position can include arithmetic operations.
• length: The number of characters to be included in the resulting substring, starting from ‘start_position’. It is also an integer value, with the maximum limit being the length of the ‘text’.

Example

SELECT SUBSTR('Oracle SQL', 8, 3) AS Substring
FROM dual;

Output

Substring
---------
SQL

Explanation

In the above SQL, the SUBSTR function is used to extract a substring from the main string ‘Oracle SQL’. The function starts extracting from the 8th character and extracts 3 characters, so the output is ‘SQL’.