2

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.

Nick Heidke
  • 2,787
  • 2
  • 34
  • 58

2 Answers2

4

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

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;
Siyual
  • 16,415
  • 8
  • 44
  • 58