sql

SQL SUBSTRING Function

In this article, we’re going to explain the SUBSTRING function in SQL.

1. Introduction

The SUBSTRING function is available in SQL Server (from 2008 edition) and MySQL (from version 4.0) as a part of String functions.

Using this function we’re able to manipulate strings directly with a SQL query. Also, in combination with other functions and procedures, the SUBSTRING function is a great tool introduced in the SQL environment.

In the next sections, we’ll see more about the syntax and some important knowledge about how to this function.

1.1 Pre-requisites

You need to have at least SQL Server 2008 edition or above for the examples shown in this article. Also, you can use MySQL starting on 4.0, but I recommend using version 8.0 or later.

2. SUBSTRING syntax

The function is called in a T-SQL instruction like this: SUBSTRING(string, start, length). Below, I explain each parameter present in the function.

  • string – the string to extract from.
  • start – the start position to extract from string. The first position is 1 as default.
  • length – the number of characters to extract. Must be a positive number.

In SQL Server, all these parameters are required to use the SUBSTRING function. A simple example is shown next:

Using SUBSTRING

SELECT SUBSTRING('Java Code Geeks',1,4)
sql substring - result
SUBSTRING result

For MySQL, we need to insert at least the string and start parameters to work. If we don’t specify the length, the function will return from the position passed as the start position to the last string character.

Using SUBSTRING without length

SELECT SUBSTRING('Java Code Geeks',4)
sql substring - mysql no length
MySQL result without length

3. Using SUBSTRING

Now, we’ll do some hands-on with the SUBSTRING function. In the next steps, we’re going to see how to scan the string together with other functions.

3.1 Using with a character string

A good reason to use the SUBSTRING function is to find some value in the string and return it to a new string. The example below is a parse for an email where we can qualify the recipient, domain, and full domain. Should work fine in SQL Server.

Using SUBSTRING with character

with substring_example as (select email='sergio.lauriano@javacodegeeks.com')
 
select email
, recipient = SUBSTRING(email,1, CHARINDEX('@',email,1) -1)
, fulldomain = SUBSTRING(email, CHARINDEX('@',email,1) +1, LEN(email))
, domainname = SUBSTRING(email, CHARINDEX('@',email,1) +1, CHARINDEX('.',email,CHARINDEX('@',email,1)) - CHARINDEX('@',email,1))
, toplevel = SUBSTRING(email, CHARINDEX('.',email,CHARINDEX('@',email,1)) +1, LEN(email))
from substring_example
sql substring - char result
SQL Server SUBSTRING char result

Note that we’ve used the CHARINDEX function to put boards to the string extraction. Firstly, we define the ‘@’ character to find its position in the string to define the recipient.

Next, we used the char position to determine the start of the full domain. Later, we combine the ‘@’ character as the start and ‘.’ character as the end to extract the domain name.

Finally, the top-level is find from the ‘.’ character until the end of the string.

In MySQL, we can use the POSITION function as an alternative to extract the character substring.

Using SUBSTRING with character in MySQL

set @email = "sergio.lauriano@javacodegeeks.com";
select SUBSTRING(@email,1,POSITION("@" IN @email)-1) as recipient, 
SUBSTRING(@email,POSITION("@" IN @email)+1,length(@email)) as fulldomain,
SUBSTRING(@email,POSITION("@" IN @email) + 1,POSITION("." IN substring(@email,POSITION("@" IN @email) + 1))-1) as domainname,
SUBSTRING(substring(@email,POSITION("@" IN @email) + 1),POSITION("." IN substring(@email,POSITION("@" IN @email) + 1))+1,length(@email)) as toplevel;
MySQL SUBSTRING char result

3.2 Using text and ntext

We can also handle table fields with SUBSTRING function. The varchar (text and ntext) data type can be used with the function and in the next example, we’ll do the same manipulation as the previous, but using a table field.

We’ll use the below table to manipulate the field CustomerMail.

CustomerIDCustomerNameContactNameContactMail
1Tampa Bay BuccaneersTom Bradytom.brady@buccaneers.com
2New England PatriotsNULLcontact@patriots.com
3Dallas CowboysDak Prescottdak.prescott@cowboys.net
4Kansas City ChiefsPatrick Mahomespmahomes@kcchiefs.com
5New Orleans SaintsNULLcontact@saints.com
Customers table

In SQL Server, we just point to the required field that we want to manipulate. The result should be the same as using a simple varchar variable.

Using SUBSTRING with varchar in SQL Server

select ContactMail as email
, SUBSTRING(ContactMail,1, CHARINDEX('@',ContactMail,1) -1) as recipient
, SUBSTRING(ContactMail, CHARINDEX('@',ContactMail,1) +1, LEN(ContactMail)) as fulldomain
, SUBSTRING(ContactMail, CHARINDEX('@',ContactMail,1) +1, CHARINDEX('.',ContactMail,CHARINDEX('@',ContactMail,1)) - CHARINDEX('@',ContactMail,1)) as domainname
, SUBSTRING(ContactMail, CHARINDEX('.',ContactMail,CHARINDEX('@',ContactMail,1)) +1, LEN(ContactMail)) as toplevel
from Customers
SQL Server varchar result

In MySQL, we do the same move keeping the script pretty similar to the previous.

Using SUBSTRING with varchar in MySQL

select SUBSTRING(ContactMail,1,POSITION("@" IN ContactMail)-1) as recipient, 
SUBSTRING(ContactMail,POSITION("@" IN ContactMail)+1,length(ContactMail)) as fulldomain,
SUBSTRING(ContactMail,POSITION("@" IN ContactMail) + 1,POSITION("." IN substring(ContactMail,POSITION("@" IN ContactMail) + 1))-1) as domainname,
SUBSTRING(substring(ContactMail,POSITION("@" IN ContactMail) + 1),POSITION("." IN substring(ContactMail,POSITION("@" IN ContactMail) + 1))+1,length(ContactMail)) as toplevel
from Customers;
MySQL varchar result

3.3 Using image

In the next example, we’re dealing with images, that in SQL Server is stored as a varbinary. The varbinary type is a hex byte representation of a file.

I created the MyImages table where some images and they’re present the following data in a simple query.

Image varbinary in table MyImages

Using the SUBSTRING function, we can retrieve the first 10 bytes of these data using the following query:

Using SUBSTRING with varchar in MySQL

select SUBSTRING(img,1,10) as imageOutput from MyImages
SUBSTRING image result

4. Conclusion

In conclusion, we saw how to use the SUBSTRING function in SQL Server and MySQL. Also, we could understand its syntax in each environment.

Further, we can do some examples together with other functions to extract data from a string and also fields from tables. Finally, we could see in a SQL Server example how the SUBSTRING function can return image bytes data.

5. Download the source code

Download
You can download the full source code of this example here: SQL SUBSTRING Function

Last updated on Feb. 23rd, 2022

Sergio Lauriano Junior

Sergio is graduated in Software Development in the University City of São Paulo (UNICID). During his career, he get involved in a large number of projects such as telecommunications, billing, data processing, health and financial services. Currently, he works in financial area using mainly Java and IBM technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button