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
('00123'),
('00456'),
('00789'),
('01012'),
('01567');
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:
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:
FROM Employee;
This should remove the leading zero characters from the string.
Conclusion
In this tutorial, we explored two main methods that we can use to remove any leading zero characters from a given string/column.
