We had a question from one of our customers today regarding the sorting of their search results. I will use a fruit for an example.

Apple A Plum Banana Orange Pear The Kiwi

Now really they should be sorted as

Apple Banana The Kiwi Orange Pear A Plum

So the 'A' and 'The' are ignored, does anyone know how to do this using the mapper?

Cheers

This is going to be complex and certainly not something I have seen the mapper do out of the box. You will need to fetch the entities and sort in memory using the a custom IComparer which ignores the "A ", "The " etc in the compare evaluations.

I too wouldn't know how to do this, even in sql (perhaps a string split, but even then I don't know if it works).

Yes, I think I'm going to do it client side. Thanks, but it was worth a stab.

You didn't say what database you are using, but there is possible method in SQL Server.

Add a computed column to the table:

create table SortTest (
ID int identity,
Description varchar(50),
SortKey as 
  case
    when charindex('a ', Description) = 1 then substring(Description, 3, len(Description))
    when charindex('the ', Description) = 1 then substring(Description, 5, len(Description))
    else Description
  end
)

Now you can use an orderby on sortKey.

Clever!

Hi, We had the same problem in the music industry for Artist names, i.e. Kate Bush would normally list under Bush and not Kate. We solved this by having a sortkey column in the table and therefore sorted on that.

example: -

Artist Bon Jovi with SortKey of Bon Jovi Artist Kate Bush with SortKey of Bush Kate Artist Jon Bon Jovi with SortKey of Bon Jovi Jon

listing would be: -

Bon Jovi Jon Bon Jovi Kate Bush

Geoff.

Guys, just add a SortOrder column to your table and always sort by that. Although it faintly rings of denormalization, it's a perfectly legitimate thing to do.