Home » SQL & PL/SQL » SQL & PL/SQL » Split value and sort by lastname
Split value and sort by lastname [message #365655] Thu, 11 December 2008 19:40 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I have a field called fullname that outputs records with fullname of people.
Here is what I have when I do this sql:
Select fullname from tableOne;
John Jones
Bill Aronsen
Sam Baker
George Williams
Dave Smith


I would like to sort in order of last name but cant figure out how to do the sql:
Bill Aronsen
Sam Baker
John Jones
Dave Smith
George Williams


Please advise.
Re: Split value and sort by lastname [message #365657 is a reply to message #365655] Thu, 11 December 2008 20:09 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
If names, have middle initials or surname like 'De Gala' or 'De Jesus' then its a different case.. But based from the sample names you have given.. Hope this will help..

Sample Data:


select empname from emp

EMPNAME

Pee-Jay Opinion
Ronel Chavez
Joanne Jimenez
Zoilo Villadolid
Richmond Roman
Gem Gantuanco
Ruth Patino
Lovely Day
Allan Aruego
Errol Obrique

-----

select empname from emp order by SUBSTR(empname,INSTR (empname,' ',1,1))

EMPNAME

Allan Aruego
Ronel Chavez
Lovely Day
Gem Gantuanco
Joanne Jimenez
Errol Obrique
Pee-Jay Opinion
Ruth Patino
Richmond Roman
Zoilo Villadolid



Thanks,
Wilbert

[Updated on: Thu, 11 December 2008 20:11]

Report message to a moderator

Re: Split value and sort by lastname [message #366177 is a reply to message #365655] Fri, 12 December 2008 00:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you assume that the last name is always last, then you can also take the suggestion from wmgonzalbo but do the substr from the other end. Consider this:

  1* select instr('abc def ghi',' ',-1,1) from dual
SQL> /

INSTR('ABCDEFGHI','',-1,1)
--------------------------
                         8

1 row selected.

  1* select substr('abc def ghi',instr('abc def ghi',' ',-1,1)+1) from dual
SQL> /

SUB
---
ghi

1 row selected.


of course, this won't help you with someone who uses professional designations or a generation suffix but hey, nobody said it would be perfect. Besides, depending upon how badly you want it, you could construct a formula that would extract the last name 99.9% of the time and that might be good enough eh?

Good luck, Kevin
Re: Split value and sort by lastname [message #366180 is a reply to message #366177] Fri, 12 December 2008 00:27 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And of course if you have to separate part of fullname this means you should have at least 2 columns and not only one.

Regards
Michel
Previous Topic: Passing CHAR literal containing more than 4000 char in function parameter
Next Topic: Is there a better way to handle null parameters?
Goto Forum:
  


Current Time: Fri Dec 09 19:09:03 CST 2016

Total time taken to generate the page: 0.26151 seconds