If my data looks like this how do I one-off update it?

First_Name Last_Name Contact_Name
---- --- ----
NULL NULL YVES MERCIER
NULL NULL YUN CHUNG
NULL NULL YUKITO YAMASAKI
NULL NULL YUICHI MOCHIZUKI
NULL NULL YUGUNDERA BHIDE

My goal is to split the full contact name into first and last as well. Because that is what is in the database and I want to keep it consistent for the few thousand records missing it.

Do I update this using a stored Procedure?

How would it be formed?

CREATE PROCEDURE dbo.ap_Insert_FirstLastName_WhereNull

SELECT *
FROM Booth_Visitors_Data
WHERE First_Name IS NULL And Last_Name Is NULL And Contact_Name IS NOT NULL
ORDER BY Contact_Name Desc

Update Booth_Visitors_Data Set

??

Or is this impossible with a stored procedure?

SHould I wirte a .aspx page to do it?

Or can I do it all in code-behind??

ProtectedSub btnUpdate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnUpdate.ClickDim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10.10.10;Initial Catalog=Leads;Persist Security Info=True;User ID=xxxxx;Password=xxxxx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="SELECT * FROM(Booth_Visitors_Data) WHERE(First_Name Is NULL And Last_Name Is NULL And Contact_Name Is Not NULL) ORDER BY Contact_Name Desc"

myCommand.BeginExecuteReader()

myCommand.Connection = con

con.Open()

con.Close()

EndSub

If I can do it in code-behind, how does it look in modern .net code as opposed to the old classic asp way?

[Edited by Dinakar Nethi]

Masked userid/pwd in connection string.

;

Does the data in your contact_Name column have only 2 words - for first name and last name or could there be any middle names, Jr, Sr or I, II, III etc too?

|||

Here's a sample:

Declare@.Namevarchar(100)

Set@.Name='YUKITO YAMASAKI'

Select FirstName=substring(@.Name ,1,charindex(' ',@.Name) )

,LastName=substring(@.Name,charindex(' ',@.Name),len(@.Name)-charindex(' ',@.Name)+1)

|||

Well there *Could be* 3, so I was thinking we could go to the far right and go back to the first space and call that the last name.

YOO JI YE
For example...

SO the first name would be Yoo JI and the last name Ye

But I guess that would not work with all the sr. jr. possiblities.

So can this all be done in stored procedures?

Can Stored Procedures loop through records?

|||

>>So can this all be done in stored procedures?

Yes.

>> Can Stored Procedures loop through records?

Ofcourse.

The bigger issue here is to identify the pattern so you can split the full name into its constituent names. The sample I provided earlier works only if its 2 words. Anything more will be tricky.

Check if this post helps:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44123

|||

It was fun coming up with this. This will work for 3 words but not for 2 words.

Declare @.Name varchar(100)

Set @.Name = 'YOO JI YE'

Select FirstName = substring(@.Name ,1, charIndex(' ', @.Name, charIndex(' ', @.Name)) - 1 + charIndex(' ', @.Name)),
LastName = SubString(@.Name, charIndex(' ', @.Name) - 1 + charIndex(' ', @.Name),len(@.name) - charIndex(' ', @.Name) - 1 + charIndex(' ', @.Name))

My suggestion would be to follow the link ndinakar provided for you.

|||

I worked with the link ndinakar provided for you abit and came up with a script for you that will work with both 2 part and 3 part full names.

If you pass in 'YOOJI YE' then you get back as a first name of 'YOOJI' and lastname of 'YE'. If you pass in 'YOO JI YE' then you get back a first name of 'YOO JI' with a last name of 'YE'

Declare @.Name varchar(100)Set @.Name ='YOOJI YE'selectcase When parsename(replace(@.name,' ','.'), 3) IS NULL Then parsename(replace(@.name,' ','.'), 2) Else parsename(replace(@.name,' ','.'), 3) +' ' + parsename(replace(@.name,' ','.'), 2)End As FirstName,
parsename(replace(@.name,' ','.'), 1) As LastName

sql