Tag Archives: special character

Break!

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
DECLARE @tbl TABLE (
    Value VARCHAR(50)
    );

-- insert two records to the table
INSERT INTO @tbl
VALUES
    ('Hello' + CHAR(32) + 'World')
    ,('Hello' + CHAR(160) + 'World')
    ;

-- select the results
SELECT DISTINCT Value FROM @tbl;

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! 🙂