SQL Server Zero Pad Left

Facebooktwittergoogle_plusredditpinterestlinkedinmail

In a recent project, I had the task of padding a number with zeros. SQL Server doesn’t have a built in Zero Pad Left function. SQL Server does have a Right function. You can use this to create a zero padded left string.

In the code above, I am take 10 zeros, appending a text value to it, then taking the right 10 characters. The end result is the same as padding the left with zeros.

 
SQL Server Zero Pad Left Function
You can take this a step further and create a scalar function out of it.

SQL Server Truncate Date

Facebooktwittergoogle_plusredditpinterestlinkedinmail

A common function that people need to do when dealing with datetimes is extracting the date from the datetime.  Oracle has a built-in function to do this called TRUNC.  SQL Server does not have this (yet).  However… you can accomplish this very easily.  Here are a few ways to perform a truncate date function in SQL Server.

 

SQL Server Trim

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

 
Trim Text Inline

The functions to trim text are RTrim() and LTrim().

 
If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

You can use this new fancy SQL Server Trim function by calling it like this:

How To Format A Number With Commas

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Server 2012 makes it a lot easier to format a number with commas, but let’s face it… previous versions haven’t been so nice to us.  Here are the easiest ways that I know to format a number with commas.

SQL Server 2012+

SQL Server 2008 And Previous