String manipulation

[edwardturner]

If I have a column in a table contain names and I want to split out what should always be the surname what SQL can I use. I want to split off from the right until I reach the first whitespace

this was - "S R WILLIAMS"
becomes "S R" and then another field called "WILLIAMS".

 

[LNBruno]

Will the name ALWAYS be two initials and a last name? If so, maybe something like this?

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]SUBSTRING[/color]([COLOR=red]'J J Doe'[/color],0,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],[COLOR=red]'J J Doe'[/color])) [COLOR=blue]AS[/color] FirstInit,
[COLOR=#FF00FF]SUBSTRING[/color]([COLOR=red]'J J Doe'[/color],[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],[COLOR=red]'J J Doe'[/color])+1,2) [COLOR=blue]AS[/color] MidInit,
[COLOR=#FF00FF]SUBSTRING[/color]([COLOR=red]'J J Doe'[/color],[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],[COLOR=red]'J J Doe'[/color])+3,len([COLOR=red]'J J Doe'[/color])) [COLOR=blue]AS[/color] LastName

[/code]

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[djj55]

Hello,
Please note that <M!ke>'s question about how the original value is presented is very important. This has been discussed in various forums and can get complicated. Some examples are SR Williams, S R Williams, Dr. S R Williams, S R Williams Jr. ... you get the idea.

Good luck.
djj