Get records sorted alphabetically, starting with a specific letter on the Sql server

Question

In SQLSERVER / MSSQL, here is the problem:

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

I want the entries to be sorted alphabetically, starting with the letter “I.”

Example result:

'Ioren' 'Iumen' Pie 'Arfen' 'Coldry'

I do not want to use union or more sql statements. Just try to catch it with the special offer.

I tried:

 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:

The complete answer for the order, starting entirely with i, and then loop back to h:

 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 

Note that this will affect performance on large tables.

Answer 2

Alternatively, this is 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 sort it alphabetically starting with 'l'

Edit This solution works for me:

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

then I filled in some test data, then ran 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]