SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS

Earlier I wrote two articles about how to efficiently copy data from SSMS to Excel. Since I wrote that post there are plenty of interest generated on this subject. There are a few questions I keep on getting over this subject. One of the question is how to get the leading zero preserved while copying the data from SSMS to Excel. Well it is almost the same way as my earlier post SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet. The key here is in EXCEL and not in SQL Server.

The step here is to change the format of Excel Cell to Text from Numbers and that will preserve the value of the with leading or trailing Zeros in Excel. However, I assume this is done for display purpose only because once you convert column to Text you may find it difficult to do numeric operations over the column for example Aggregation, Average etc. If you need to do the same you should either convert the columns back to Numeric in Excel or do the process in Database and export the same value as along with it as well.

However, I have seen in requirement in the real world where the user has to have a numeric value with leading Zero values in it for display purpose. Here is my suggestion, instead of manipulating numeric value in the database and converting it to character value the ideal thing to do is to store it as a numeric value only in the database. Whatever changes you want to do for display purpose should be handled at the time of the display using the format function of SQL or Application Language. Honestly, database is data layer and presentation is presentation layer – they are two different things and if possible they should not be mixed.

If due to any reason you cannot follow above advise and you need is to have append leading zeros in the database only here are two of my previous articles I suggest you to refer them. I am open to learn new tricks as these articles are almost three years old. Please share your opinion and suggestions in the comments area.

SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display

SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS

  1. Pingback: SQL SERVER – Removing Leading Zeros From Column in Table « SQL Server Journey with SQL Authority

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  3. Leading zeroes to the left of a numeric are often required for things like an Employee ID Number which must have the same number of characters for all records. There is a problem when pasting to Excel and an ID of, say, ‘005726’ gets truncated to 5726. Since this is an ID, there will never be the need to perform math functions on it, so AVG, SUM, etc. are irrelevant. There is not an inutitive way that I know of to preserve those leading zeroes unless you format the entire Excel sheet as Text BEFORE you paste the data in.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s