Removing hidden characters
Have you ever received a request to write a query to get the data you need from a table in SSMS (SQL Server Management Studio) only for it to display differently when copied into excel? You probably know what I mean. Rows get split in the middle of a field which then shifts everything after it into the next field. Suddenly you have street names in your postcode field and created dates in your notes field.
You look back at your query and can see that it is displayed correctly in SSMS. You scroll through the whole dataset and see no issues at all. Did it become corrupt during the copy and paste. Does excel have an issue that needs a hotfix to be applied?
Is it broken?
The answer of course is no. There is nothing wrong with excel or SSMS they are both doing exactly what they were designed to do. The problem is in the data itself. Applications don’t just store the text you can see. In order to display it correctly they also store hidden characters which are used by the application to remember the formatting of that text. There are quite a few hidden characters that can cause issues. You can google for a complete list of them but the three that seem to cause the most problems are CHAR(9) which is TAB, CHAR(10) which is LF or Line Feed and CHAR(13) which is your CR or Carriage Return.
So what are these mysterious characters, what do they do and most importantly how can I remove them so that they don’t mess with my data export to excel? I’m glad you asked as I recently had to do the same task. As I have seen this issue before it was a quick fix and the BA (Business Analyst) was very pleased with a fast solution. So here it is.
SELECT REPLACE(REPLACE(REPLACE(fieldname, CHAR(13), ”), CHAR(10), ”),CHAR(9),”)
This simply replaces the TAB, CR and LF from that field with nothing. Essentially removing them from your dataset and allowing you to copy and paste the results into excel for your user.
Note you will need to do this for all the fields that are causing your data to shift fields in your dataset.