CODEWITHSUNDEEP
sqlsql-serverregexsql-order-by
apelliciari
apelliciari

Reputation: 8501

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?

Upvotes: 4

Views: 7353

Answers (3)

Dog Ears
Dog Ears

Reputation: 10015

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

Upvotes: 2

cjk
cjk

Reputation: 46425

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.

Upvotes: 3

LukeH
LukeH

Reputation: 269358

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

Upvotes: 2

Related Questions