Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-23 : 10:36:50
|
| Derek Wilson writes "Hi Guys, this isn't a question, but an answer to a problem I had difficulty solving. I thought you might want to post the solution to your site.The problem was how to pull only the last name from a field called 'CEO Full Name'. Update [Business_Data]Set [CEO Last Name] = CAST(Right(RTrim([CEO Full Name]), (PatIndex('% %', Reverse(Rtrim([CEO Full Name])))-1)) as Char(20))Where (PatIndex('% %', Reverse(Rtrim([CEO Full Name])))-1) > 0I needed to use the Cast function becuase I was updating a field which was only char(20).The 'CEO Full Name' was a Char(60), which contained trailing blanks, resulting in the need for the RTRIM function.The 'full_name' field could contain nulls, blanks, which resulted in the inclusion of the where cluase, to eliminate an invalid length parameter.The PatIndex finds the first space position.My main break through in solving the problem came when a SQL Server instructor of mine (Dan Emery) informed me of the Reverse function. Hope you find it useful.Derekderek_d_wilson@yahoo.com" |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-05-23 : 11:14:37
|
| That would work for everyone with a simple name like 'John Smith', but will not be correct for the likes of 'Johan van der Merwe'.May be best to handle those records with more than one space differently ?? |
 |
|
|
|
|
|
Re: Pulling Last Name from a Full Name field.