Split combined Name column 5

[sparkbyte]

I need to split a name field.

Contacts
ContactID ContactName ContactPhone ContactFax ContactCellPhone ContactEmail OfficeCode Program
5041 Smith, Jane M 866-555-1212 866-555-1313 ADM USR
7389 John.Jones@somewhere.com 866-555-2345 866-555-5678 ITS PGP


basicaly something like split [Smith, Jane M] into
lastname = Smith ; firstname = Jane M
or if
Name like *@* then split Jane.smith@somewhere.com
into ContactLastName = Jones ; ContactFirstName = John
AND
ContactEmail = John.Jones@somewhere.com

Can someone assist??

I can do this quite easily in VBS but just need a start on how to do this in SQL.

Declare @Temp Table(ContactID  int, ContactName VarChar(50))
Insert Into @Temp Values(5041,'Alfred, Leslie M')
Insert Into @Temp Values(7389,'Jane.smith@somewhere.com')

Select  ContactId,
        ContactName,
        Case When ContactName Like '%@%' 
                  Then Left(ContactName, CharIndex('.', ContactName)-1)
             When ContactName Like '%,%' Then Left(ContactName, CharIndex(',', ContactName)-1)
             End As FirstName,
        Case When ContactName Like '%@%'
                  Then SubString(ContactName, CharIndex('.', ContactName)+1, CharIndex('@', ContactName)-CharIndex('.', ContactName)-1)
             When ContactName Like '%,%'
                  Then Right(ContactName,Len(ContactName)-CharIndex(',', ContactName)-1)
             End As LastName,
        Case When ContactName Like '%@%' Then ContactName End As EmailAddress
From    @Temp

This worked Great.

Then found email addresses without periods.

Fix

Select  
        ContactName,
        Case When ContactName Like '%@%' and ContactName Like '%.%'
                  Then Left(ContactName, CharIndex('.', ContactName)-1)
             When ContactName Like '%,%' Then Left(ContactName, CharIndex(',', ContactName)-1)
             End As ContactFirstName,

        Case When CharIndex('@', ContactName) > CharIndex('.', ContactName)
                  Then SubString(ContactName, CharIndex('.', ContactName)+1, CharIndex('@', ContactName)-CharIndex('.', ContactName)-1)
             When ContactName Like '%,%'
                  Then Right(ContactName,Len(ContactName)-CharIndex(',', ContactName)-1)
             End As ContactLastName,

        Case When ContactName Like '%@%' Then ContactName End As ContactEmail

from dbo.Contacts

Then needed a way to update the table with the split data.


suggested updating one column at a time as so.

--  Extract eMaiil Addresses from ContactName
Update  dbo.Contacts
Set     ContactEmail = Case When ContactName Like '%@%' Then ContactName End
                       Where   ContactEmail is NULL or ContactEmail = '

Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[markros]

Brad's function is obviously going to be slower since it goes character by character. BTW, don't recall mentioning it as faster...

PluralSight Learning Library

 

[sparkbyte]

Most excellent!!! Even handles last name with dashes!!!!!

That fixed 485 entries out of 33370 contact names.


I look forward to your blog on this function.

If you have a good blog or reading suggestion for normalizing tables I am looking for some reading. It has been around 5 years since I have actively done SQL and have done minor databases design and have been 'given' a project to get rid of all the Access DB's on our network shares. There are about 18 of them in regular use that will need to be combined (I hope to be able to combine at least 12 of them since they all center around work flow management here.) Most of which were created by past management types. NOONE with any DB knowledge that's for sure!!

Thanks a Million.

Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[gmmastros]

There are tons of articles about normalizing tables. Personally, I think the one written by Rudy (r937) is a really good one. He recently (within the last year or two) wrote a SQL book, too. Still waiting for my autographed copy.

http://www.r937.com/Relational.html

I have several blogs that I've written. You can see the list of them here:

http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=10

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Yea I say Rudy's recent book on Amazon! It's getting good reviews. Especially for web developers.

I have a quick question in regards to all these functions.

If the function is one that will be usefull in more that one DB do you put it in the [master] db or create the function in each of the DB's that you need it in, or is there some other trick that would be handy??

Thanks!!

Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[gmmastros]

Personally, I never put anything in the Master database. Other people may have different ideas though. I sell commercially available software to many different organizations. My customers vary in size, so some of them are using SQL Express on a desktop and some are using "real" servers with databases that handle different applications. If it's not in my database, I don't want to rely on it being there.

What you could do is put the function in your user databases and ALSO put the function in the MODEL database. The Model database is one of those system supplied databases. It acts as the model for new databases that you create on your server. When you create a new database, all of the tables, functions, procedures, etc.... that exist in model will also exist in the new database. You can read more about that here:

http://blogs.lessthandot.com/index....rver/automatically-create-a-sql-server-table-

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Hey George, you ever get into the KC area? If so it would be great to meet you sometime.

BTW. I love your blog on Less-Than-Dot. Thanks for the liks to it.

And thanks for all the help. I am sure as this project move on I will have several more questions.

Right now I am putting together a proposal to get approval to merge as many of the Access DB's on our network as possible into one work flow management system. I hope I am not biting off more than I can chew. But most of the Access apps here all track client files in some fashion and generate reports for management on either a daily or monthly basis for employee performance evaluations.

I’ll keep my fingers crossed!!


Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[sparkbyte]

Oh, forgot.

I still am having trouble getting first, middle, last out of an email address.

john.q.public@doman.com

Is there a way to modify one of the queries we were working with to handle that??



Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[gmmastros]

I have never been to the KC (Kansas City?) area. I rarely travel for business. When I do travel, it's usually with my wife and daughter. That being said, let me know if you're ever in the Philadelphia area.

As far as splitting your names in to first, middle and last names... You may be better off writing a series of custom functions to do this. This thread has touched on many of the string handling functions, so you should have a good basis to work with. Give it a try and let me know if you have any more problems.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bradschulz]

LOL... I'm not surprised at the timing results of the various PROPER functions.

I mainly wrote the functions I wrote as a quick exercise (and mainly so that I could utilize them in my Random Sentence Generator). I didn't put a lot of thought into making them as speedy as possible.

I like George's approach using PATINDEX.

--Brad

 

[gmmastros]

Brad,
I hope I didn't say anything that may have offended you. Truth is.... I often wonder if I spend too much time optimizing code. For example, I don't have a use for a proper function anywhere in my application, yet I spent about 30 minutes optimizing this process for speed. As a "1 off" data cleanup process, speed isn't very important. I wouldn't be surprised if someone else creates a version faster than mine. There's always a better mouse trap out there.

Anyway, I respect you for all of your contributions to the SQL community. Keep up the good work!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bradschulz]

>
>I hope I didn't say anything that may have offended you.
>

Oh gosh, not at all.

I just had to laugh when I saw the statistics on the timings of the functions... 21573 vs 1796... Eek!

Anyway, thanks for your kind words.

Best...

--Brad

 

[NiceArms]

This is a great thread.

I have no reason to use this information right now but I have added to my archive as I am sure that at some point in the future I will have to reference it.

Thank you both for the clairty in your steps.

/Nice

 

[gmmastros]

As promised, I wrote a blog about the ProperCase function. You can read it here:

SQL Server Proper Case Function

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

A little bit different problem, bu may be helpful too

http://social.msdn.microsoft.com/Fo...l/thread/41c46d2b-4f94-49c6-a629-5ab23b279e46

PluralSight Learning Library

 

[sparkbyte]

Mark, Love the post!!

I've been a bit tied up on another project and just getting back to this.

The query from the other thread would be great if I could add the ability to handle names like.

Smith, John Q III

Rith now it only handles proper names.
ie. John Q Smith III

Any ideas?


Here is the code from the other posting.

-- Test query
declare @t table (ID int identity primary key, FullName varchar(100))
insert into @t 
select
'Lawrence Wright III'
union all select
'Brenda Castillo'
union all select
'Ann R Williams'
union all select
'Kevin'
union all select
'Suman M Gupta Jr'

union all select
'Gupta Jr, Suman M'

--select FullName, REPLACE(REPLACE(REPLACE(FullName,'  ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),'') AS SpacesRemoved from @t
--charindex
--select substring('test',NULLIF(CHARINDEX(' ','test'),0),LEN('test'))  

;with Split as (select id, FullName, 1 as WordNum, CAST('' as varchar(100)) as PrevWord,
                rtrim(ltrim(SUBSTRING(FullName,1,CHARINDEX(' ',FullName + ' ') )))  as Word,
                ltrim(SUBSTRING(FullName, nullif(CHARINDEX(' ',FullName),0) + 1, 100)) as Remainder
                from @t
                union all 
                select id, FullName, WordNum + 1 as WordNum, CAST(Word as varchar(100)) as PrevWord,
                rtrim(ltrim(SUBSTRING(Remainder,1,CHARINDEX(' ',Remainder + ' ') ))) as Word,
                 ltrim(SUBSTRING(Remainder, nullif(CHARINDEX(' ',Remainder),0) + 1,100)) as Remainder
                 from Split where LEN(Remainder) > 0 and WordNum <= 4         
                 )
   --    select * from Split order by ID, WordNum
                 
select Split.ID, FullName, max(case when WordNum = 1 then Word end) as FirstName,
Max(case when WordNum = 2 and Len(Word) = 1 then Word end) as Initial,
Max(case when WordNum = 2 and Len(Word) > 1 then Word when WordNum = 3 then Word end) as LastName,
Max(case when WordNum = 4 then Word when WordNum = 3 and Len(PrevWord) > 1 then Word end) as Suffix 
from Split 
 group by Split.ID, Split.FullName 
 
select ID,FullName,FirstName,MiddleInit,LastName,Suffix
from @t
cross apply (select NameWork=replace(FullName,' ','|')+'||||') F1
cross apply (select p1=charindex('|',NameWork)) F2
cross apply (select p2=charindex('|',NameWork,p1+1)) F3
cross apply (select p3=charindex('|',NameWork,p2+1)) F4
cross apply (select p4=charindex('|',NameWork,p3+1)) F5
cross apply (select FirstWord=substring(NameWork,1,p1-1)
                   ,SecondWord=substring(NameWork,p1+1,p2-p1-1)
                   ,ThirdWord=substring(NameWork,p2+1,p3-p2-1)
                   ,FourthWord=substring(NameWork,p3+1,p4-p3-1)) F6
cross apply (select FirstName=FirstWord 
                   ,MiddleInit=case when len(SecondWord)=1 then SecondWord else '' end
                   ,LastName=case when len(SecondWord)=1 then ThirdWord else SecondWord end
                   ,Suffix=case when len(SecondWord)=1 then FourthWord else ThirdWord end) F7

As you can see on number 6 if the format changes to the "LastName Suffix, FirstName MiddleInit" it is greatly miss handled.

[tt]
ID	FullName		FirstName	MiddleInit	LastName	Suffix

1	Lawrence Wright III	Lawrence			Wright		III
2	Brenda Castillo		Brenda				Castillo	
3	Ann R Williams		Ann		R		Williams	
4	Kevin			Kevin			
5	Suman M Gupta Jr	Suman		M		Gupta		Jr

6	Gupta Jr, Suman M	Gupta				Jr,		Suman
[/tt]

Thanks

John Fuhrman