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.
Search Keys
- sql mid function
- mid sql server
- sql mid string
- sql server mid string function
- sql server substring
- sql substr
- substring sql
- sql query substring
- sql substring function
- sql select substring
- sql server substring example
- sql substring example
- substring access
- 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 |
5 |
SQL for students |
185 |
2007 |
Performance |
Siva Kumar |
6 |
Oracle Cookbook |
136.33 |
2014 |
Optimization |
Vidyavathi |
7 |
Simply Oracle |
140 |
2013 |
Security |
Sakunthala |
8 |
How to Write Accurate SQL Code |
150 |
2010 |
Performance |
Ranjani Mai |
9 |
My SQL Complete Reference |
90 |
2011 |
Optimization |
Ramanathan |
10 |
SQL Visual Quickstart |
160 |
2015 |
Performance |
Vinoth Kumar |
11 |
Getting Started With SQL |
170 |
2010 |
Database |
Keshavan |
12 |
SQL and Relational Theory |
99.99 |
2008 |
Performance |
Ramanathan |
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 |
5 |
stude |
forma |
6 |
Cookbo |
imiza |
7 |
Oracle |
urity |
8 |
Write |
forma |
9 |
Comple |
imiza |
10 |
ual Qu |
forma |
11 |
Start |
abase |
12 |
Relat |
forma |