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 |
|
AlexCold
Starting Member
39 Posts |
 Posted - 2002-06-28 : 18:06:26
|
| Hi, Folks!I have a problem! :-(I have a table where first, middle, last and maiden names are stored in the single field liketblMyWeakTable-------------------ID | Name-------------------1 | Alex [A] Puritche [Maiden]I enclosed optional fields in square brackets. So each record can contain from 2 to 4 words (actually, last name as well as first name Can contain more then 1 word)Now!Boss neesd me to order query result by Last Name.Is here a way to do it?If no - please, use as lees technical jargon as possible explaining why it is impossible, because I'll send him url to this topic.If yes - I'm all ears!!!!-- Success is a journey, not a destination!!FAQTeam memberhttp://www.faqteam.org/ |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-06-28 : 19:45:07
|
I hate to say anything is impossible, but here's a plain English explanation of why it is very difficult to be accurate...First, I'm assuming that the square brackets were just hints to us, and that your data does not really contain square brackets around middle name and around maiden name. If it does, then this becomes dramatically easier. However, given that assumption, here's one challenge: If the name field contains 3 words, it appears from what you've told us that there is no possible way to know whether that is the persons First Middle Last or First Last Maiden or possibly event First LastPart1 LastPart2 (e.g. Martin Van Buren). Unless there is some other indicator, then the best you can do with this database design is take an 80/20 whack at it. (That is look for a solution that will give you the right results 80% (or more) of the time, such as assuming that 3 words is always First Middle Last and accepting the fact that sometimes it is not.)I would suggest investing the time into fixing your database design and populating it with the proper values. To do this, you'll have to do an 80/20 attempt like described above, and then do manual adjustments to clean up the 20% that is wrong. Once it's cleaned up, you'll lead a much happier life.  |
 |
|
|
|
|
|
|
|
Urgent!!!