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

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?

From stackoverflow

• 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.

  avastreg : == gives error, but with one = it works! thank you

  Neil Barnwell : Remember to check the performance is acceptable though, especially if you have or are likely to have a large dataset.

  ck : Yeah sorry about that, am coding in C# at the mo, but I've edited it to be right.

  Neil Barnwell : BTW - very clever solution - trés bien!

  Dog Ears : Does it actually work?

  ck : @Dog Ears: See [== gives error, but with one = it works! thank you – avastreg (49 mins ago)]

  Dog Ears : I'm missing the point but don't you want the data to order like this.. l m n o....g h i j k Does the selected answer do that?

  LukeH : @Dog Ears, You are correct. The OP suggests that they need the ordering I-ZA-H. ck's answer doesn't deliver this, your answer and mine do. Note though that it's an I (i), not an l (L).

  ck : @Dog Ears, Luke: Answer updated with full answer

  avastreg : good work guys! :)

• 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
  

  ck : +1: nice answer, very clean, but does use separate element in select statement

  Dog Ears : My edit works perfectly but doesn't uses the order by clause.

  Dog Ears : ... *now* uses (not doesn't) the order by clause!

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

  ck : +1: Nice answer, but LIKE clause could be quite slow on large data sets, but possible not as slow as mine :) (however I assume this is a small lookup table)

  LukeH : @ck, The LIKE isn't ideal, but it should still be able to use any index on the [Language Code] column. I suspect that using SUBSTRING, ASCII, UPPER etc would necessitate a table scan.

  LukeH : @ck, I agree that it's most likely a small lookup table. However, even if every drop of performance isn't vital, I think that my answer is more readable than all those string functions.

  ck : You answer is indeed more readable. I like the fact there are three different answers to this question.

  Dog Ears : I like this answer as I imagine it's more tolerant of different collations.