TSQL to combine Lastname and First Initial

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 2005 Forums

Transact-SQL (2005)

TSQL to combine Lastname and First Initial

Author

Topic

Programmer2007
Starting Member

4 Posts

Posted - 2007-08-16 : 14:17:40

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:

John Anderman, AndermaJ
Robin Williams, WilliamR
Jay Foot, 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.

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 = ' '

Any help would be highly appreciated.
Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-16 : 14:51:26

Here's the easy part, I broke it down into small pieces. The hard part is when Jennifer Love Hewitt gets a login, or Madonna or John Jacob Jingleheimer Schmidt come aboard. All of a sudden finding the last name and first name isn't so easy.
Good Luck!

Jim

DECLARE @Name varchar(100)
DECLARE @space int
DECLARE @Login varchar(100)

SET @Name = 'robin Williams'
SET @space = CHARINDEX(' ',@name)

SET @Login = SUBSTRING(@name,@space+1,100)+LEFT(@Name,1)
select @login

Programmer2007
Starting Member

4 Posts

Posted - 2007-08-16 : 15:00:52

I should make a correction to my first post, the FULL NAME column is in the order of LastName FristName.

So in your solution this should be like this:
@name = 'Williams Robin' instead of 'Robin Williams'

And if @name = 'Tony Rick', then our logic will fail since it willy try to grab the 7 characters counting the SPACE as well. So the result will be : "Tony Ri R", which is wrong. It should be 'Tony R'

Thanks.

Programmer2007
Starting Member

4 Posts

Posted - 2007-08-16 : 15:08:56

Thanks Yak, 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 your solution and mine are almost same but 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 mine 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 17:07:19

SELECT LEFT(dbo.fnParseString(1, ' ', Col1), 7) + LEFT(Col1, 1)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 17:14:36

dbo.fnParseString can be found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

Programmer2007
Starting Member

4 Posts