The LTRIM function in T-SQL only removes whitespaces. But, in some instances, you need to do more complex trimming. The trick is to use the PATINDEX function in conjunction with the STUFF function. PATINDEX will return the index of the first occurence of a specified pattern. In my example below, PATINDEX will return the index of the first non-zero character. STUFF takes 4 parameters:
– the affected column
– the the starting point of the deletion
– the length of the deletion
– a string to insert at the deletion point
So the code below will delete all characters, starting at the first, and finishing before the first non-zero character. Why not use SUBSTRING instead? Because STUFF is a little more robust than SUBSTING and will not raise an error on you if the PATINDEX returns -1.
SELECT STUFF(myColumn, 1, PATINDEX('%[^0]%', myColumn) - 1, SPACE(0)) FROM mytable
Also, if you happen to have a column of type float and you want to get the values without the leading zeros (for example, 0.75 would become .75), you simply need to cast your column as varchar and you’re set.