Newbie question..Need help 4

[dnfrantum]

I have a Name field that is first and last name concatenated, but separated by a space. I need to separate them into their respective fields. Can someone provide me with the functions that I would use to accomplish this?



Thanks in advance,
Donald

 

[ClaireHCM]

Say you have a table called mytable with Name field in that

select left(Name,charindex(' ',Name)-1),
right(Name,len(Name)-charindex(' ',Name)) from mytable

 

[bertrandkis]

Very good answer by ClaireHsu, but if you want to move each part of the name into an new table where they reside in different columns,do it this way:

Insert into MyNewTable(FirstName,Lastname)
select left(Name,charindex(' ',Name)-1),
right(Name,len(Name)-charindex(' ',Name)) from mytable

Now if the 2 new columns will be in the same table as the table where the concatenated column is,you will do an update this is how you can do it:
--First add the new columns Firstname and lastname
ALTER TABLE Mytable ADD FirstName varchar(50),LastName varchar (50)
--Secondly add an identity column,you will drop this column later,this column will have autogenerated number from 1 to x depending on the number of rows you have in your table, if you already have an IDENTITY column use it.
ALTER TABLE Mytable ADD TempColmun int IDENTITY
--Decalre some variables
DECLARE @Counter int,@fname varchar(50),@lname varchar(50)
--initialize the counter
SELECT @Counter=1
--Loop through your table
WHILE @counter<=(select count(*)from Mytable)
BEGIN
--Split the name and get the firstname and lastname into
--variables
SELECT @fname=LEFT(Name,CHARINDEX(' ',Name)-1),
@lname=RIGHT(Name,len(Name)-CHARINDEX(' ',Name)) FROM mytable WHERE TempColumn=@counter
--Now update your table
UPDATE Mytable SET FirstName=@Fname,LastName=@LName
WHERE TempColumn=@counter
--Increment the counter
SELECT @Counter=@counter+1
END
--Now remove the temp column
ALTER TABLE MyTable DROP COLUMN TempColumn
--If you want to get rid of the concatenated field
ALTER TABLE MyTable DROP COLUMN Name

I hope this helps
Black Belt Sequelist

 

[JamesLean]

bertrandkis,
Your second query (to perform an update to the same table) is a very long-winded method. Why not just:

UPDATE mytable
SET firstname = LEFT(name, CHARINDEX(' ', name) - 1),
  lastname = RIGHT(name, LEN(name) - CHARINDEX(' ', name))

--James

 

[dnfrantum]

The Left worked well, but the right didn't work at all.

Thanks in advance,
Donald

 

[ClaireHCM]

create table mytable(name varchar(30),firstname varchar(20),lastname varchar(20))
go
insert into mytable(name) select 'Nelly Merrill'
insert into mytable(name) select 'Carol Henson'
insert into mytable(name) select 'Sandra lynch'
go
update mytable set
firstname =left(Name,charindex(' ',Name)-1),
lastname =right(Name,len(Name)-charindex(' ',Name)) from mytable
go

 

[dnfrantum]

what if there is more than one occurrance of the space in the string, will that cause it not to work properly? The right is still not working in the select that I am attempting and this is the only thing that I can think of. Any help is appreciated.

Thanks in advance,
Donald

 

[ClaireHCM]

Did you change something?I exactly paste what I ran in query analyzer.And it works really fine.Why you cant just copy what every I paste it and dont change anything?

BTW,you didnt say what kind of error you are getting in the right part....

 

[dnfrantum]

Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.

I don't need to do a create or insert, I only want to do a select.

Here is a sampling of the data:


Annette Block
Att: Accounts Payable
Mary Jo Koski
<NULL>
8989 S Orange Ave

As you can see there is no consistency as to the type of data that I might encounter, so I am not sure if your select will still work. Once again, it works just fine for the first half or LEFT portion of the field.

Thanks in advance,
Donald

 

[bertrandkis]

Dear JamesLean,
An update statement without a where clause the way you showed it will affect all the records in the table and is some thing that will cost you your job.
The script that I proposed allows the updation of all rows in the table 1 row at a time,If there were 1000 rows in the table you wouldn't want to issue 1000 commands, your manager will not appreciate it,and may not give you salary increase.The solution will be to loop through all records, that's the reason why my script is like that.
Thanks for your comment.

 

[JamesLean]

I don't think I'd be too worried about updating 1000 rows at a time (especially an update as relatively simple as this)!

I agree that for large updates you may want to split it into batches. Have a look at this FAQ which explains how to do it in batches rather than one at a time (which is perhaps being a little over-cautious):

How to Use Batch Size to Speed Mass Updates, Inserts and Deletes
faq183-3141

--James

 

[geoffswaby]

I use this code to extract the last word in a concatenated string - it gets round the 'mulitple spaces' issue:

select reverse(substring(reverse(rtrim(name)),1,charindex(' ',reverse(rtrim(name))))) from mytable

 

[osjohnm]

bertrandkis,
James's post was a suggestion and it would be up to the person who started this thread to add the necessary WHERE criteria. Another thing is that dnfrantum didn't say whether this was to do a global update or whether this needs to seperate the columns in a select on a regular basis. The search criteria wasn't supplied either and neither was any table structure.
I'm assuming that James understood that this was a global update(James can correct me if I'm wrong)

James has a point especially if this is going to be a select that runs frequently.

Also you would need to continously keep MyNewTable insync with MyTable as new records will be added all the time.
Something to keep in mind

dnfrantum, I would recommend that you post your table structure and give us a bit more info on what the process is surrounding this. You should always supply as much info as you can so that we can assist you better.

My 20c worth would be that maybe a function would be in order and you rather call the function to &quot;split&quot; the names.

As for losing jobs etc this wouldn't happen as everbody should atleast know that you would first attempt this in a dev instance, then the staging/qa instance and lastly once you are happy you would apply the changes to the live instance. Of course you would also make an adhoc backup of the live database before you attempted any such update.
This should be a standard practice or atleast become one if procedures like this don't already exist. James is a professional and from what I can see in his threads and posts, he has been around the sql server block a few times.

We are all trying to help and what someone posts should never be used straight away as even experts might make a mistake. The onus is on the person who started the thread to test scripts before applying them to a production database. We try to make our posts as accurate as possible but we can't be held liable for a lack of change control procedures.

John