Developping for the translation industry RSS 2.0



 Monday, 26 May 2008

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.


Monday, 26 May 2008 16:49:50 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL

Navigation
Advertisement
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2018
Stanislas Biron
Sign In
Statistics
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2018, Stanislas Biron