T-SQL Tuesday #002: Houston, we’ve had a non-breaking space here

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.

No Comments

2 Trackbacks

Leave a comment