Custom sort in SQL?

[meester]

I have a series of book titles that I am trying to sort in<br>
my sql query. I would like to be able to ignore "The", "A" and other common words at the start of a field<br>
<br>
e.g.<br>
<br>
"The Old Man and the Sea" should be sorted under "O" not "T".<br>
<br>
Is it possible to write a custom sort in SQL, or do I need to create a separate row with the common headings removed, and then sort on that row?<br>
<br>
e.g.<br>
<br>
title = "The Old Man and the Sea"<br>
sort_title = "old man and the sea"<br>
<br>
Thanks!<br>
<br>
:meester<br>
<br>
<br>
<br>
<br>
<br>
<br>

 

[Shobin]

This suggestion it only works for one book at the time, but you can create a cursor to calculate the Position, and then instead of doing the Select substr... create view as Select substr...<br>
<br>
Declare <br>
Position number(2);<br>
<br>
Begin<br>
Select instr(title,' ') <br>
into Position<br>
from Books<br>
where num_book = 432;<br>
<br>
Select substr(title,Position+1,length(title))<br>
into Title_order<br>
from Books<br>
where num_book = 432<br>
order by 1;<br>
<br>
Good Luck

 

[buksh]

I'm only starting out but I reckon that to use a CASE within a select statement might give you the ability to select books with titles where the case is true that the title begins with<br>
Case Title_name like A%<br>
Case Title_name like The%<br>
<br>
and so on.<br>
You could then re-store the truncated titlenames in a new column after removing the appropriate number of characters from its name. This means you retain the original full title should it be needed.<br>
Sorting on your new coulmn should then be a breeze.<br>
Sorry I'm not good enough to send actual code.<br>
Paul<br>
<br>

 

[MikeLacey]

Ok.<br>
<br>
I do something similar with 'phone numbers. People enter phone no's in strange ways "(nott) 556 243" or "(01522)-556-243" would be typical but the expect to be able to search for those numbers by entering "01522556243". Hmmm.<br>
<br>
I created a "hidden" database column which is never displayed and fill that automatically when a record is created or updated - stripping out the characters I don't want so that people can search on it later.<br>
<br>
So try this.<br>
1) Create a new column called HIDDEN_TITLE.<br>
2) Create a database trigger on that table which will get fired when a row is inserted or updated.<br>
3) Fill your new column when that trigger fires, using the original title as your starting point, look at the first 4 characters of your title and, if it's 'The ', chop them off and add them to the end. You then end up with a column that contains "Old Man of the Sea., The" and you can write a statement that looks like this:<br>
<br>
Select TITLE, AUTHOR<br>
From BOOKS<br>
Order By HIDDEN_TITLE;<br>
<br>
Hope this sorts you out.<br>
<br>
Regards<br>
<br>
Mike<br>
---<br>
Mike_Lacey@Cargill.Com<br>

 

[connectxyzzy]

Or you could try<br>
<br>
Select<br>
decode(upper(substr(Title,1,4)),'THE ',<br>
substr(Title,4,len(Title)-4)), etc......<br>
<br>
for each word you want to eliminate. Notice the blank space is included in the test as to not eliminate theater and others....<br>
<br>
Using the instr function may also help.......