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


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.

You can also search these topics using sql server mid string function, sql server substring, sql query substring, sql substring function, sql select substring, sql server substring example, sql substring example, substring sql example.

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
You can also search these topics using sql substring index, substring sql query, sql substring function example, sql substring where, sql search for substring, sql substring charindex, sql find string position, sql string position, sql position function, sql string functions with examples.