CODEWITHSUNDEEP
sqlsql-server
Nick Heidke
Nick Heidke

Reputation: 2847

Override SQL Server Default Sort Order

In order to correctly 'match' data to another data source, I'm looking to add something (perhaps a collation?) to this query in order to return results A-Z 0-9 instead of 0-9 A-Z as they are now.

SELECT TOP 1 order_id
FROM orders
WHERE order_id IS NOT NULL
ORDER BY order_id

Note that order_id is a nvarchar field.

For this example, I'm getting order 04394 instead of order ASK284 as I'm looking for.

Upvotes: 2

Views: 211

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

This will force Alpha over Digits

SELECT TOP 1 order_id
FROM orders
WHERE order_id IS NOT NULL
ORDER BY case when order_id like '[0-9]%' then 1 else 0 end
        ,order_id

Upvotes: 4

Siyual
Siyual

Reputation: 16917

You can do this by ordering on a CASE expression with each condition:

Select      Top 1
            order_id
From        orders
Where       order_id Is Not Null
Order By    Case When Order_Id Like '[A-Z]%' Then 0 Else 1 End,
            Case When Order_Id Like '[0-9]%' Then 0 Else 1 End,
            Order_Id Asc;

Upvotes: 0

Related Questions