Help With TSQL To Combine Lastname And First Initial From Full Name

Hello fellows,

I am trying to write a query, here is the detail:

I have a table of users. Table schema:
FULL NAME, LOGIN

example data:

Anderman John, AndermaJ
Williams Robin, WilliamR
Foot Jay, FootJ

The LOGIN field will be my resulting field after I run my query.

It has a "FULL NAME" column, and a "LOGIN" column. I want to extract the first 7 characters of LAST Name from the FULL NAME field and extract the First Name Initial(just first letter of the first name) from the FULL NAME field and then set the LOGIN field with the resulting combination. The FULL NAME column is in the order of LastName FirstName.


Here is what I have been trying to write:

Declare @spacePos as int

Select @spacePos = charindex(' ', fullname) From TIUSER

Update TIUSER

SEt LOGIN = ltrim(Substring(fullname, 1, 7)) + Substring(fullname,@spacePos+1,1),

where @spacePos <> 0 and LOGIN = ' '


Currently we only have last and first names in our database and we will keep it that way since we don't store middle names and hopefully that will not be an issue.

But it seems that my solution was not working since if the last name is less than 7 characters then it should only grab upto the SPACE and not beyond that , but it was grabbing beyond the SPACE so how do you fix that?

That is I want atmost 7 characters from LAST NAME but incase of less than 7 then upto the space found.


Any help would be highly appreciated.