Creating login names - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating login names

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-12-31 : 13:45:40

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(Cast(ParseName(replace(LastName,'.',''),1) as char),7)
else
LEFT(FirstName, 1)+ LEFT(LastName,7)
end as UserID
from Security.dbo.tblUserInformation

The parsename 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

jhermiz

3564 Posts

Posted - 2005-01-01 : 01:55:46

Hi Laura,

I'm definately no t-sql expert, but I think the replace function will work for you.

Try this:



SELECT REPLACE('abc de',' ','about to replace that white space')
GO

The 1st parameter is your string.
The 2nd parameter is what to search for to replace.
The 3rd parameter is what to replace it with.

If you want to replace it with nothing try passing '' as the 3rd parameter

A new beat on the web -- http://www.web-impulse.com

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-01 : 03:13:41

parsename just returns an entry from a list (of up to 4) separated by "."'s.

If you hust want to get rid of spaces and .'s

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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-01-03 : 06:48:22

Thats perfect thanks so much

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources