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

Search Keys

  • substring example
  • sql substring index
  • substring sql query
  • sql substring function example
  • sql substring where
  • sql search for substring
  • sql substring charindex
  • substr replace
  • substring length
  • sql find string
  • sql find string position
  • sql string position
  • sql position function
  • sql string functions with examples