I have a SQL Table with a column called FullName which contains, for example, "John Smith".
How can order the data by the last name that appears in the FullName column?
For a long name like "Laurence John Fishburne", I would like to order the data by the word "Fishburne".
Thus, names are stored in the order
I am using Microsoft SQL Server 2005.
I would do something like:
SELECT FullName
FROM TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE(FullName), 0, CHARINDEX(' ', REVERSE(FullName))))
Instead of calculating what the last name is each time you want to run the query, you can have a computed column that persists the actual value into a column that can be used as you would any other column.
ALTER TABLE Customer
ADD LastName AS
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) PERSISTED
This Adds the column LastName to your Customer table, uses the function specified to calculate the value of the last name, and stores it onto the physical table. The keyword PERSISTED at the end is required for the value to be saved to the disk, else it will be computed each time a query is run.
Edit: To deal with values with no spaces:
ALTER TABLE Customer
ADD LastName AS
case when CHARINDEX(' ', REVERSE(FullName)) > 0
then RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)
else
FullName
end
PERSISTED
Though you can fiddle with this function as you will to determine what happens in this case. My point is to show that case statements can be used. You may also want to cast all output paths to the same type to avoid type mismatches.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With