What is the difference between a breaking space and a non-breaking space? About five minutes of frustration.

Let’s take a look at the potential confusion. First, let’s create a temporary table and insert two records, one with a breaking space, and another with a non-breaking space. We’ll use the ASCII codes to ensure we get it right (and because it will allow you to copy from this post and try it yourself).

-- declare a temporary table
    Value VARCHAR(50)

-- insert two records to the table
    ('Hello' + CHAR(32) + 'World')
    ,('Hello' + CHAR(160) + 'World')

-- select the results

Notice anything funny about the result set? Yup—in the normal grid view, the records look identical, and yet they are not collapsed by the DISTINCT statement. Unless you have non-breaking spaces on the mind, that may be an unexpected result.

The good news is there’s an easy way to check for non-breaking spaces. Simply copy the result grid and paste it to the query editor. You’ll notice that the non-breaking space is visually identified as a solid red line. Thus it’s a rather easy difference to check for.

And one of the best things about this example? No clean-up needed! 🙂

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: