This month I’m participating in T-SQL Tuesday for the first time. T-SQL Tuesday is a really great idea and I hope to participate regularly.
The topic for this month is a puzzling situation. My puzzle is loosely related to T-SQL, but that’s where I came across it. A few months ago I was working on a data integration project that involved extracting data from many different Excel files and loading a SQL Server database. The data format was far from perfect, so I had to load those Excel files into some staging tables and run some T-SQL code before loading the destination database.
For example, all phone numbers had to consist of 10 digits (with a leading zero). All hyphens, spaces, braces had to be removed. Quite an easy task – just a few REPLACEs and I could move on. But I could not – some of the updated records still had spaces in the phone numbers. I did a triple check of my REPLACE functions and I was sure that the code should work fine.
Then I came up with a new idea: I copied the character that looked like a space from the query results and executed:
SELECT ASCII(' ')
The result was: 160. Not 32. So the space wasn’t just an ordinary space. It turned out to be a non-breaking space. Seems obvious, but how many of us know that the ASCII table contains a non-breaking space?
It is also worth noticing that the TRIM function does not remove non-breaking spaces. Surprising, isn’t it?
Another puzzling situation I had to deal with was a case where a stored procedure randomly timed out when executed from a custom application, but when executed from SSMS – it always completed in just a few seconds. I blogged about it last month.