Sorting varying sizes of blank strings in SQL Server

Question

In one of my queries, I was trying to sort by a column that had a varchar data type.
I wanted to show columns that had a space after the columns that had empty strings, but the output of ORDER BY did not work as I expected it to (in fact it surprised me).

Here is a SQL FIDDLE that shows the problem.

I have researched this issue and the only partially relevant article I found is this.

What I want to know is why would SQL Server do this, and what can I do to force the sorting to consider these values as different?

Thanks in advance.

@GoatCO Thanks, I have been searching for an explanation for hours and could not find one. — user2989408, Feb 06 '14 at 22:27

score 2 · Answer 1 · answered Feb 06 '14 at 22:13

2

Use the DATALENGTH() function

SELECT *
FROM Tab
ORDER BY DATALENGTH(val)

answered Feb 06 '14 at 22:13

Anon

10,660
1
29
31

+1 Hadn't come across that function before. http://sqlfiddle.com/#!3/e5b924/9/0 – Hart CO Feb 06 '14 at 22:17
Unfortunately this will not work for me, as my column has other values too. – user2989408 Feb 06 '14 at 22:19
Can you `ORDER BY val, DATALENGTH(val)` ? – Anon Feb 06 '14 at 22:36
@Anon +1 Yes that would work. Although, I was looking to get an answer that kind of explained why this was happening, and it seems that the reason is explained in the post mentioned by Goat CO? – user2989408 Feb 06 '14 at 22:41

score 1 · Accepted Answer · answered Feb 06 '14 at 22:11

1

You can differentiate in the ORDER BY by replacing a space with something:

SELECT * 
FROM Tab 
ORDER BY LEN(REPLACE(val,' ','X'));

Demo: SQL Fiddle

As for why SQL Server behaves this way I can't shed any light.

answered Feb 06 '14 at 22:11

Hart CO

34,064
6
48
63

I was more interested in knowing why this was happening? If it was just about sorting, I could have replaced the space with a character and achieved that. Also my column has values too, it saves user names. – user2989408 Feb 06 '14 at 22:16
@user2989408 The 'why' seems to be answered here: http://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space If you need help in getting sorting to go a certain way then post some sample data. – Hart CO Feb 06 '14 at 22:21

Sorting varying sizes of blank strings in SQL Server

2 Answers2