Generate Initials based on FirstNames

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)

Generate Initials based on FirstNames

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-22 : 10:43:21

I have a table "Member" with two fields.
FirstName varchar(50),
Initials varchar(10)

The initials field is null and I need to generate initials from the firstnames.
E.g. Peter John -> PJ

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 10:52:53

select substring(colname,1,1) + substring(colname, charindex(' ', colname)+1,1)
from myTable

you can extend this to 3 names...

Go with the flow & have fun! Else fight the flow

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-22 : 10:57:00

This may help...


create table #t (firstname varchar(50))
insert into #t select 'Peter John'

select substring(firstname,1,1) + substring(firstname,charindex(' ',firstname)+1,1) from #t

drop table #t

EDIT: I should refresh before posting... Work obviously gets in the way...

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-22 : 11:04:35

Thanks
This works for situation where there are members with two names e.g . Peter John
but where there is only one firstname
e.g. Peter it gives the result 'PP'.

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-22 : 11:12:17

--two firstnames
UPDATE Member
SET Initials=substring(Firstnames,1,1) + ' '+substring(Firstnames, charindex(' ', Firstnames)+1,1)
WHERE initials ='' and charindex(' ', Firstnames)>0

--one firstnames

UPDATE Member
SET Initials=substring(Firstnames,1,1)
WHERE initials ='' and charindex(' ', Firstnames)=0

I get this to work for both.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 11:12:20

[code]
Declare @myName varchar(100)
Set @myName = 'Corey Aldebol'

Select
case when parseName(Replace(@myName,' ','.'),3) is not null then Left(parseName(Replace(@myName,' ','.'),3),1) else '' end +
case when parseName(Replace(@myName,' ','.'),2) is not null then Left(parseName(Replace(@myName,' ','.'),2),1) else '' end +
case when parseName(Replace(@myName,' ','.'),1) is not null then Left(parseName(Replace(@myName,' ','.'),1),1) else '' end

Set @myName = 'Corey'

Select
case when parseName(Replace(@myName,' ','.'),3) is not null then Left(parseName(Replace(@myName,' ','.'),3),1) else '' end +
case when parseName(Replace(@myName,' ','.'),2) is not null then Left(parseName(Replace(@myName,' ','.'),2),1) else '' end +
case when parseName(Replace(@myName,' ','.'),1) is not null then Left(parseName(Replace(@myName,' ','.'),1),1) else '' end

Set @myName = 'Corey Seventhnight Aldebol'

Select
case when parseName(Replace(@myName,' ','.'),3) is not null then Left(parseName(Replace(@myName,' ','.'),3),1) else '' end +
case when parseName(Replace(@myName,' ','.'),2) is not null then Left(parseName(Replace(@myName,' ','.'),2),1) else '' end +
case when parseName(Replace(@myName,' ','.'),1) is not null then Left(parseName(Replace(@myName,' ','.'),1),1) else '' end
[/code]

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 11:20:55

yeah... i really need to remember parseName function... it really great for such things.

Go with the flow & have fun! Else fight the flow

pjn
Starting Member

27 Posts

Posted - 2004-09-22 : 15:50:54

Didn't even know PARSENAME existed - I can already see a use for this in some of my apps - thanks