June 11, 2002 at 8:29 am
Hello All, How is everyone today?
I have a table in my SQL 2000 database that contains the following columns - FirstName, MiddleName, LastName My problem is that the FirstName column contains the entire Full name of the person, but not in all records. (Seems that the original creater of the table caught their mistake, but only after about 35,000 records.)
How can I update the MiddleName and LastName Columns with a portition of the data that is in the FirstName column? Not all records contain a MiddleName or Middle Initial. See the Example below:
Bad Data at this time, need to correct.
FirstName MiddleName LastName
Mindy Sipple <NULL> <NULL>
Jean E. Smith <NULL> <NULL>
Joe Robinson <NULL> <NULL>
Brad <NULL> <NULL>
Michael Allen Hall <NULL> <NULL>
I need to Update the Records that are incorrect, so that the MiddleName (Initial) and LastName are filled with the proper data of the user. And at the Same time, truncate the MiddleName (Initial) and LastName from the FirstName Column, like the example below:
What I need to the table to look like
FirstName MiddleName LastName
Mindy <NULL> Sipple
Jean E. Smith
Joe <NULL> Robinson
Brad <NULL> <NULL>
Michael Allen Hall
Any and all assistance with this will be greatly appreciated.
Thanks
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
How long a minute is....
Depends on what side of the bathroom door you are on.
June 11, 2002 at 8:47 am
Andrew,
Here is something that I wrote for a similar problem that you should be able to use as a baseline for fixing your data.
Hope this helps. Format will look weird from pasting it in here but once again it should get you started.
select
UserFullName,
rtrim(upper(substring(substring(UserFullName, 1, charindex(' ', UserFullName)), 1, 1))+lower(substring(substring(UserFullName, 1, charindex(' ', UserFullName)), 2, 32))) as FirstName,
rtrim(substring(substring(UserFullName, charindex(' ', UserFullName)+1, charindex(' ', UserFullName, charindex(' ', UserFullName))), 1, 1)) as EmpMI,
rtrim(upper(substring(ltrim(substring(UserFullName, charindex(' ', UserFullName, charindex(' ', UserFullName))+2, 32)), 1, 1))+lower(substring(ltrim(substring(UserFullName, charindex(' ', UserFullName, charindex(' ', UserFullName))+2, 32)), 2, 32))) as EmpLast,
rtrim(upper(substring(UserName, 2, 1))+lower(substring(UserName, 3, 32))) as EmpLast2
from Table1
where
rtrim(upper(substring(ltrim(substring(UserFullName, charindex(' ', UserFullName, charindex(' ', UserFullName))+2, 32)), 1, 1))+lower(substring(ltrim(substring(UserFullName, charindex(' ', UserFullName, charindex(' ', UserFullName))+2, 32)), 2, 32))) !=
rtrim(upper(substring(UserName, 2, 1))+lower(substring(UserName, 3, 32)))
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 11, 2002 at 1:53 pm
Sorry David
This did not work for me. It is not seperating the data accordingly. Thanks for the effort.
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
How long a minute is....
Depends on what side of the bathroom door you are on.
June 11, 2002 at 6:17 pm
You may need to do this in a WHILE loop or CURSOR. But the CHARINDEX is a good approach. You need to look for ' ' so you know where to seperate items. If you need a hand with this let me know but consider creating a CURSOR of the records you need to fix. Check the record for ' ' (also RTRIM initially to be sure no extra spaces on end). Then using 3 variables parse into as many as there are spaces and left over data without spaces. Then if you have one varibale you assume firstname or lastname and fill the field and NULL the reset. If two variables then Firstname and Lastname NULL middle, and three variables fill all three. You just have to build the parsing and if logic to make decisions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply