So I’ve the following problem. In one column, I store author information. This information is retreived from Amazon.com’s WebServices, and as such, it passes all the author info at once. I’ve no way of knowing what is a first name, last name, etc. To make things more complicated, some books (in particular, the Penguin Classics), also include other authors after the primary author. So I get data like:
- Jane Austen
- F. Scott Fitzgerald
- Charles Dickens, Charles Montgommery
On the other end, I’d like people to be able to browse books by author. Ideally, I’d like to be able to sort by author last name. So I’ve figured that I could try this out.:
- If there is a comma, use the word directly before the first comma to sort on.
- If there isn’t a comma, use the last word in the string to sort
This seems like it should be fairly straight forward. Unfortunaly, my knowledge of SQL Server 2000 functions is sadly thin. Could anyone out there provide any insight into this? My current idea was to do this inline within the ORDER BY clause, but I suppose once there’s IF/ELSEs, it may need to become a function that I simply call. Of course, I don’t really know how to write a UDF fro SQL Server.
UPDATE (11:43 am): I’ve solved the problem, with a nice little UDF. May not be as optimized as it could be, but it works, it’s easily readable and in tests, is currently 100% accurate.