Link to home
Start Free TrialLog in
Avatar of UniqueData
UniqueDataFlag for United States of America

asked on

Split full name into two fields

I have a field that contains last name an undertimined number of spaces (maybe not always the same) then first name.

what would the statement look like that will pull the two items apart into two fields (I'm hoping they never have Sr., etc in between)
Avatar of chapmandew
Flag of United States of America image

Post some sample data....
Avatar of UniqueData


Ahh, you posted too quick.  I was about to delete the question.  Turns out last name is the first 16 characters (padded with spaces).

So it will be RTrim(Left(FullName,16)) as LastName, RTrim(Right(FullName,Len(FullName-16))) as FirstName

You're long as the first 16 characters are always the lastname.
oops, that was giving me troubles because there were trailing spaces after the first name.  I changed it to...

RTrim(Left(STAFF_NAME,16)) as LastName, Right(RTrim(STAFF_NAME),Len(RTrim(STAFF_NAME))-16) as FirstName

and it worked.  There may be a cleaner way.
Its never clean to parse out long as it works for you, use it.  :)
Just a thought, if there are 32 characters overall, first 16 for last name, last 16 for first name, your last query computes the length after RTRIM so if there were 5 spaces at the end that are removed the total number will be 27 minus 16, you are starting at index 11 and if there is a last name that filled all of the first 16 characters, 4 last letters of it would be cut into the first name, no?

Is it always certain that space(s) separate the last and first name and the last name does not contain characters? If yes, perhaps something like the code snippet below might work:
SELECT LTRIM(SUBSTRING(FullName, CHARINDEX(' ', FullName), LEN(FullName))) AS FirstName
      , RTRIM(SUBSTRING(FullName, 1, CHARINDEX(' ', FullName))) AS LastName
FROM TableName

Open in new window

The only problem with the the last post is I now get spaces after the LastName.  I tried to change it to LTrim but that made it worse.
Avatar of LordOfPorts
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did it perfectly.