How to strip out the last name in a field

[bobpv]

Hello all,

I am working with a SQL 2000 database. One table hold employee names, in a varchar(28) filed. The names are stored in a single string seperated by a blank space (such as Joe Brown).

What I would like to do is pull out the last name only(the right of the space). I am aware of a method to do this in Crystal Reports by created a replacement set of strings, but am hoping I can do it inside the Stored Procedure and pass it over from the procedure directly as a field.

Any advice would be appreciated. Thanks in advance.

Bob
Robert Pevey and Associates

http://www.peveyweb.com

 

[dbomrrsm]

select substring('Joe Brown',patindex('% %','Joe Brown')+1,50)

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

If its the case that someone may have a middle name 'Joe Steven Brown' the above would give you Steven Brown as it is searching for the first space from the front of the string. If this is a possibility then you are best using the below which searches for the first space looking from right to left:

SELECT LTRIM(RIGHT('Joe Brown',(SELECT PATINDEX('% %',REVERSE('Joe Brown')))))

[blue]DBomrrsm[/blue]