Removing leading zeroes from a field in a SQL statement

Understanding the fundamentals

PATINDEX and SUBSTRING to the rescue

Can't convert a text string to a number? Worry not, conjure up the powerful PATINDEX and SUBSTRING spells:

SELECT SUBSTRING(your_field, PATINDEX('%[^0]%', your_field), LEN(your_field)) FROM your_table; -- Let's play 'Where's Waldo?' with zero


PATINDEX hunts down the position of the first non-zero character, whereas SUBSTRING gets you everything after that. Ensure the SUBSTRING length parameter matches the original column length. Gotcha, right?

Walking the tightrope removal of zeroes

Let's consider some situations which require more finesse:

• For alphanumeric fields, the TRIM or CAST command won't cut it, so use PATINDEX and SUBSTRING.
• Spaces? Not fun when they're leading the pack. Kick them out with LTRIM before zero removal techniques.
• Variable length of zeroes don't have to ruin your day. The above methods handle them gracefully bringing uniformity to your result set.

Watch out for these edge cases

• Mixed content fields: Ensure your zero removal doesn't cause an unwanted identity crisis in your data.
• Zeroes within the string: Not your concern. They can stay without causing a ruckus.
• Leading zeroes in numerical strings meant to be text: Handle with care. Do not cast to an integer.

Scratching beyond the surface

What about decimals?

For decimals with leading zeroes, use CAST or CONVERT:

SELECT CAST(your_field AS DECIMAL(10, 2)) FROM your_table; -- Starting the Decimal Dance with precision and scale


Remember to denote the precision and scale correctly to avoid squashing valuable digits.

Byte-sized tips for the restless coder

• CAST is a no-no for strings with characters. Use PATINDEX and SUBSTRING instead.
• Thumbs-up to being cautious with international formats or fields used in indexing or joining.
• Size matters. PATINDEX can trigger full table scans, so use them wisely.