SQL MID() | SUBSTRING() OR SUBSTRING() Function

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;

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:

Sample Database Table - Books

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: