SQL MID() | SUBSTRING() OR SUBSTR() Functions
The SQL MID() | SUBSTRING() function is used to extract substring or number of characters from a given input string or data column.
The SQL MID() | SUBSTRING() function is extracts data or substring from anywhere in the strings.
The SQL MID() | SUBSTRING() function is supports or work with character and numeric based columns.
It can be used in any valid SQL SELECT statement as well in SQL where clause.
Related Links
SQL MID() | SUBSTRING() Syntax
For MS Access / MY SQL / ORACLE
The basic syntax is used to extract substring for a given input string or text expression:
SELECT MID(string or text, start, length);
The below syntax is used to extract substring from a specific table column value:
SELECT MID(column_name1, start, length) FROM table_name1;
| Parameter Name | Description |
|---|---|
| column_name | Required. The column to extract characters or substring from. |
| start | Required. Specifies the starting position (starts at 1) from where to start reading characters. |
| length | Optional. The number of characters to extract or return. If left blank the parameter, the SQL MID() function returns the rest of the text. |
For SQL SERVER / MY SQL
The basic syntax is for a string or text expression:
SELECT SUBSTRING(string or text, start, length);
The below syntax is for a specific table column value:
SELECT SUBSTRING(column_name1, start, length) FROM table_name1;
SQL MID() | SUBSTRING() Example - Using Expression Or Formula
The following SQL SELECT statement will extract number of characters (user defined) of a given input string or text:
For MS Access / MY SQL / ORACLE
SELECT MID('WWW.SimmanChith.COM', 5) AS 'Extract chars from 5th position to last char(s)';
The result of above query is:
| Extract chars from 5th position to last chars |
|---|
| SimmanChith.COM |
SQL MID() | SUBSTRING() Function More Example
| Input Value | Result |
|---|---|
| MID('Sql DataBase', 3) | l DataBase |
| MID('SimmanChith', 8, 2) | hi |
| MID('Sub String', 7, 4) | ring |
| MID('Suresh Babu', 1, 6) | Suresh |
Sample Database Table - Books
| BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
|---|---|---|---|---|---|
| 1 | Foundations Of Sql Server 2008 | 123.45 | 2012 | Optimization | Azaghu Varshith |
| 2 | Oracle PL/sql By Example | 120 | 2006 | Optimization | Pandurengan |
| 3 | SQL Fundamentals | 110 | 2015 | Database | Hanumanthan |
| 4 | Database Systems Using Oracle | 165 | 2014 | Security | Chandra |
SQL MID() | SUBSTRING() Example
The following SQL statement selects the first three characters from the "BookName" column and the first two characters from the "BookPrice" column from the "Books" table:
For MS Access / MY SQL / ORACLE
SELECT BookID,
MID(BookName, 8, 6) As '6 Chars(s) Starts From 8th Position',
MID(DomainName, 4, 5) As '4 Char(s) Starts From 5th Position'
FROM Books;
For SQL SERVER / MY SQL
SELECT BookID,
SUBSTRING(BookName, 8, 6) As '6 Chars(s) Starts From 8th Position',
SUBSTRING(DomainName, 4, 5) As '4 Char(s) Starts From 5th Position'
FROM Books;
The result of above query is:
| BookID | 6 Chars(s) Starts From 8th Position | 4 Char(s) Starts From 5th Position |
|---|---|---|
| 1 | ions O | imiza |
| 2 | PL/sql | imiza |
| 3 | dament | abase |
| 4 | e Syst | urity |
Related Links