The above is a reasonably simple way of doing it; also, cut and pasting from a cell in the grid into the editor in SSMS/Query Analyzer does the same thing; you can see that there's something else there.

From the article, however, I spot what I consider to be the real issue:

"I found the list for countries on Wikipedia. I copied the first 4 columns of the table, dropped them into Excel to clean them up and imported the result into a SQL Server table. I created the table and used the wizard to pull the data in."

While the tally table exercise was entertaining, what I see as the real issue is that the data that was imported into SQL Server was never actually inspected. In cases like this, I generally have two "standard" ways of doing things:

1) Generate a text file for a BULK INSERT/BCP... then pull it up in a hex editor (HxD or your favorite) to see what's what. This instantly shows you everything, right down to end of lines, end of pages, and so on.

2) Use EXCEL to generate INSERT statements, i.e.

=CONCATENATE("INSERT INTO table VALUES ('",A1,"')")

Again, you'd see the extra characters instantly between the tick marks in your CREATE statement.

I try to always look at the source data first; looking at the end result generally takes long.

That said, an interesting use of the tally table character splitter technique.