CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
Arturo Torres Sánchez
Arturo Torres Sánchez

Reputation: 2907

SQL Order by lexicographical order, but with longer string first

In SQL Server, I have a table with a column I want to sort. If I apply the usual ORDER BY, it does the following, as expected:

banana
bananaphone
car
carpet
dishes
//etc

However, I would like to have them sorted this other way:

bananaphone
banana
carpet
car
dishes
//etc

Still lexicographically, but if the letters match and one is longer than the other, place the longer one first.

Is it possible to do such a query in a T-SQL?

I don't even know if this sorting algorithm has a name, I couldn't find it.

Please note that the data in this post is just an example, and I will be storing user-defined data.

Upvotes: 2

Views: 19219

Answers (1)

nathan_jr
nathan_jr

Reputation: 9292

This seems to work:

declare @YourTable table (YourColumn varchar(100));
insert into @YourTable

select 'bananaphone' union all
select 'banana' union all
select 'carpet' union all
select 'car' union all
select 'dishes' union all
select 'carpet' union all
select 'cars' union all
select 'CARP'

select  YourColumn
from    @YourTable
order
by      YourColumn+replicate(char(255), 100-len(YourColumn));

Upvotes: 6

Related Questions