In one of my content type, we are storing names in the Title field. For example, title is "John Smith". Due to the recent requirement change we now need to sort results by last name.
But since the name is stored in Title how can I sort by last name in the view? Is there a way I can create a field which automatically gets the last word in the title? Or may be some other way that I am missing out?
This is probably not best solution, but it should work:
Try Views PHP. With this module you should be able to create sorting in view which will take only last word of given string (content of your title field) with PHP code (something like this example).
This module allows the admin account (user 1) to add fields, filters and sorts to views which use PHP code.
I'm making an assumption on the Last Name field. If you got the first and last names from a user which is related to the nodes being listed in the view, then you can add a View: Relationship to the User entity maybe then order the query by the related user's last name.
hook_node_presaveor some similar hook. There you split up the title and store the last word in the new last name field. You could also use Computed Field for doing this. Then resave all existing nodes to have the logic kick in.