Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ripples
Starting Member
5 Posts |
 Posted - 2003-07-24 : 09:24:35
|
| I'd like to sort a query by second name, using a field that stores the first and second names separated by a space.Can anyone point me in the right direction?Many thanksPeter |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-24 : 09:43:36
|
| Alter your database design to store the two fields separately.-------Moo. :) |
 |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-07-24 : 09:47:03
|
| try...order by right(person, len(person)-charindex(' ', person, 1))substitute person for the name of the column.hth,Justin"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!" |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-24 : 09:50:16
|
| If you do do that, be careful about things like single names, and multi part names. Unfortunately now that you have the garbage in your database it may well be a manual process to filter it all.-------Moo. :) |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-07-24 : 10:39:58
|
| It will work against multipart last names (i.e. Mimi Van Dorn) because the charindex returns the first instance of the space. However a name like John Paul Jones will cause erroneous ordering. It would be better if the field could be split out into different columns.Justin"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!" |
|
|
|
Ripples
Starting Member
5 Posts |
Posted - 2003-07-24 : 12:05:16
|
| Thanks to you both!I agree with Moo, I should really reconfigure the fields, but unfortunately the knock on effect to other code would keep me busy for weeks.I did test Justin's code and it works perfectly.Thanks again SQL Team!Best wishesPeter |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-24 : 12:10:29
|
quote:
I agree with Moo
That's Mr. MOOAnd you know what, I bet a VIEW would help you lessen the impact!Brett8-) |
|
|
|
|
|
|
|
|
How do I sort on part of a field?