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)

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)

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

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;

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.

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

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;

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.

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

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

Last updated on Feb. 23rd, 2022

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.