Create login names without spaces

Afternoon,

I've got this security database that needs to create a login name using first initial and 7 characters of the lastname. No problem.

I wrote this:

select
CASE WHEN LastName like '%.%' then
LEFT(FirstName, 1)+left(replace(LastName,'.',''),7)
else
LEFT(FirstName, 1)+ LEFT(LastName,7)
end as UserID
from Security.dbo.tblUserInformation

The replace and case functions I used to get rid of the '.' Like the name St. Clair now I get "GSt Clai" I need it to also get rid of the space but am stuck. Any thoughts would be appreciated.

Thanks

Laura

try using concanate to join the two names together like:

CONCAT(FirstName,LastName) or something or other!

There are better ways to do this, but this will work. I have taken your Line:

LEFT(FirstName, 1)+left(replace(LastName,'.',''),7)

And modified it like this:

LEFT(FirstName, 1)+left(replace(replace(LastName,' ',''),'.',''),7)


So instead of replacing the period in LastName, you are replacing the Period in replace(LastName,' ','') which is lastname without the space.