How to trim leading zeroes in a column/string in SQL

If you have been using this:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))

to get your zeroes removed from a string, you might be interested in a variation that only removes the leading zeroes. The T-SQL Function has been created to be called from either Stored Procedure code or Triggers.

--print dbo.ufn_TrimLeadingZeros ('0100 ATN652')
ALTER FUNCTION [dbo].[ufn_TrimLeadingZeros] ( @Input VARCHAR(50) )
RETURNS VARCHAR(50)
AS
BEGIN
    --RETURN REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
    RETURN SUBSTRING(@Input, PATINDEX('%[^0]%', @Input+'.'), LEN(@Input))
END
Advertisements