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 |
|
rkumar28
Starting Member
49 Posts |
 Posted - 2005-11-21 : 15:55:31
|
| Hello,I have a scenario where I have a tableA that has a NAME field. I have to grab the first name and last name from this field and insert into another tableB which has First Name and Last Name columns.Basically I want to take the First Space from the right as last name and rest of them as firstname. Is the a way a substring function can parse the first space from the right.I want to achive something like below:TableA has:NAME FIELD______ron decosta mathews m perryBiran C MconnaryI want tableB asFirstName FIELD .........LastName FIELD________________ ......._________________ron ...............decostamathew m ...........perryBrian C .............Mconnaryjoe m ...............BOEKE I have used dots just for formatting purpose.Will appreciate any advice.ThanksRaj |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 16:16:27
|
| You need to checkout the string function CHARINDEX and REVERSE in books on line and it will show you the format to find the location of the spaces. (Reverse will return a reverse of the string, and then CHARINDEX will find the first space in it. You can then use RIGHT starting at the LEN - CHARINDEX that you returned.In your example your expected results violate the question you asked. In that according to your definition the last row should return 111 as the last name, but that is not what you put in your expected output. So I didn't try to put the command together since it wouldn't work for your actual data/illustration of expectations. |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 16:17:40
|
| No fair editing it half way through my explantion ... I'll look into it now that you have corrected the data ... |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 16:28:07
|
| The following seems to work for me ... give it a shot.rtrim(convert(char, right(myName, charindex(' ', reverse(myname) )-1 ) )) as LastName,rtrim(convert(char, substring(myName, 1, len(myname) - charindex(' ', reverse(myname))))) as FirstName |
|
|
|
rkumar28
Starting Member
49 Posts |
Posted - 2005-11-21 : 16:30:55
|
quote:
Originally posted by druer
No fair editing it half way through my explantion ... I'll look into it now that you have corrected the data ...
Hi Druer,Thanks for replying. I am sorry I had to edit the last portion. I removed last name that had 111 in it as I just need the first space from the right as a last name and remaining as first name. Let me try looking online on the functions you mentioned.ThanksRaj |
|
|
|
rkumar28
Starting Member
49 Posts |
Posted - 2005-11-21 : 16:46:27
|
quote:
Originally posted by druer
The following seems to work for me ... give it a shot.rtrim(convert(char, right(myName, charindex(' ', reverse(myname) )-1 ) )) as LastName,rtrim(convert(char, substring(myName, 1, len(myname) - charindex(' ', reverse(myname))))) as FirstName
Thanks Druer..That perfectly worked for me....ThanksRaj |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-21 : 17:00:56
|
| Why are you leaving the middle initial concatenated with the first name? Are you going to store Rebecca De Mornay's first name as "Rebecca De" and her last name as just "Mornay"? |
|
|
|
|
|
|
|
|
Parsing the Name Field