How to Remove Leading Zeros from a VARCHAR Field in SQL?

Tricks to remove leading zeros in SQL

When extracting data from the database, you often encounter fields with leading zeros, which brings inconvenience to data processing. This article explains how to remove leading zeros from VARCHAR fields using SQL.

Suppose there is a field called "CustomerNumber" with data like '00001A'. To eliminate leading zeros, you can use the following method:

SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber), 10)

This statement uses the SUBSTRING function to extract non-zero characters in the "CustomerNumber" field. Here’s how it works:

• The PATINDEX function finds the position of the first non-zero character ('%[^0]%') in a field.
• The position of this character serves as the starting position of the SUBSTRING function (PATINDEX('%[^0]%', CustomerNumber)).
The
• SUBSTRING function extracts a substring of length 10 (assuming the field is VARCHAR(10)) starting with a non-zero character, effectively removing leading zeros.

The above is the detailed content of How to Remove Leading Zeros from a VARCHAR Field in SQL?. For more information, please follow other related articles on the PHP Chinese website!