SQL Standard

Remove the Leading Zeroes in SQL

When dealing with real-world data, it is often messy and dirty. This means that it can contain unnecessary or invalid data that can hinder the operations and the tasks that we wish to perform on it.

One of the most common unclean data that you might encounter is the one where the data contains leading zeros in numeric or string data. This can occur when importing the data from external sources or using the automated data collection techniques.

Leading zeroes in data can be problematic especially when dealing with numeric values that are stored as strings.

In this guide, we will discuss all the methods and techniques that we can employ to remove any leading zeroes within an SQL dataset.

Sample Data Setup

Before we dive into the applications and examples, let us start by setting up a basic dataset that will help to demonstrate the occurrence of leading zeros.

Consider an employee’s table with the data as shown in the following:

    EmployeeID VARCHAR(10)
INSERT INTO Employee (EmployeeID) VALUES

In this case, the employee ID is of “varchar” type. However, the values contain leading zeroes.

Let us explore the methods that we can use to remove these leading zeroes.

Using the CAST Function

One of the methods of removing any leading zeroes is by converting the string into a numeric type. We can use a function such as CAST or CONVERT() as demonstrated in the following example:

SELECT CAST(EmployeeID AS INT) AS EmployeeIDWithoutZeros
FROM Employee;

This should work automatically in databases that support the cast() function.

However, in databases such as MySQL, you may need to port the function to the trim function to actually remove the leading zeroes. You can then convert the values into numerical type as demonstrated in the following example:

FROM Employee;

The resulting output is as follows:

Using the LTRIM Function

The most common and powerful method that you can use to remove any leading zeroes is using the LTRIM() function. This function allows us to remove any leading whitespace characters in a given string.

However, it does not allow us to specify the specific character that we wish to remove. In our case, we can use it by removing the leading zeros from a string.

An example is as follows:

SELECT LTRIM(EmployeeID, '0') AS EmployeeIDWithoutZeros
FROM Employee;

This should remove the leading zero characters from the string.


In this tutorial, we explored two main methods that we can use to remove any leading zero characters from a given string/column.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list