Get records ordered alphabetically starting by a certain letter in Sql Server

Question

In SQLSERVER/MSSQL, here's the problem:

SELECT * from [Translation Color] order by [Language Code]

I want records ordered in alphabetical order starting by the 'I' letter.

Example of result:

'Ioren' 'Iumen' 'Tart' 'Arfen' 'Coldry'

I don't want to use union or more sql statements.. just try to catch it with an order by special clause.

I've tried with:

ORDER BY <field> REGEXP '^I' DESC

but it didn't work.

Any ideas?

Answer 1

This should do it:

ORDER BY CASE WHEN SUBSTRING([Translation Color],1,1) = 'l' 
     THEN 1 ELSE 0 END DESC

EDIT:

Full answer for ordering completely starting at i, then looping back round to h is:

ORDER BY CASE WHEN ASCII(UPPER(SUBSTRING([Translation Color],1,1))) < 73 
         THEN ASCII(UPPER(SUBSTRING([Translation Color],1,1))) + 26
         ELSE ASCII(UPPER(SUBSTRING([Translation Color],1,1))) END ASC,       
         [Translation Color] ASC

Please note that this will affect performance on large tables.

Answer 2

Alternatively is this any good:

select [Translation Color], 
  case when [Translation Color] < 'l' then 1
                     else 0 
                     end as Priority
from t1 
order by Priority, [Translation Color]

This will order it alphabeticly starting at 'l'

Edit This solution seems to work for me:

create table t1 ( c1 varchar(20) collate SQL_Latin1_General_Cp437_CI_AS)

then i populated with some test data then run this:

select c1 
from t1 
order by case when c1 >= 'l' then 0 else 1 end, c1

Answer 3

SELECT *
FROM [Translation Color]
ORDER BY
    CASE WHEN [Language Code] LIKE '[I-Zi-z]%' THEN 0 ELSE 1 END,
    [Language Code]