Changing Text to Numbers based on data 1

[MichaelF81]

In table "users_Merge" I have an "empNum" (which contains their employee number), "fname", "lname", "mgrNum" (which currently contains the managers name in the "lname, fname" format). I need a query that will split the current "mgrNum" field, find the user and replace the current data in that field with the correct "empNum" of the manager.

Thoughts?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

Can you post the example data and desired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

Ok, here is an example of the current data

Currently:
empNum lname fname mgrNum
102 Doe John Doe, Jane
103 Doe Jane Doe, John

Needed:
empNum lname fname mgrNum
102 Doe John 103
103 Doe Jane 102

But there are hundreds of rows.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

What if you have:

empNum lname fname mgrNum
102 Doe John Doe, Jane
103 Doe Jane Doe, John
104 Doe John Doe, John

?
Which John you must choose
(Don't tell me you didn't have duplicate names )
If this is the format the query isn't much difficult.

UPDATE MyTable SET MgrNumTemp = Tbl1.EmpNum
FROM MyTable
INNER JOIN MyTable Tbl1
ON MyTable.LName = LEFT(Tbl1.MgrNum, CHARINDEX(',', Tbl1.MgrNum)-1) AND
   MyTable.FName = SUBSTRING(Tbl1.MgrNum, CHARINDEX(',', Tbl1.MgrNum)+2,500)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Sorry, I post this before previewing it. MgrNumTemp must be the new field with the same type like EmpNum. The you could just drop old MgrNum and rename this to MgrNumTemp to MgrNum.
TEST THIS first and make a good backup.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

Oops, yea we do have middle names, look at the new quote

empNum lname fname MI mgrNum
102 Doe John R. Doe, Jane
103 Doe Jane N. Doe, John R.
104 Doe John S. Doe, John S.

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

DECLARE @MyTable TABLE (empNum int, lname varchar(20), fname varchar(20), MI varchar (2),mgrNum varchar(50), NewMgrNum int)
INSERT INTO @MyTable VALUES (102,'Doe','John','R.','Doe, Jane N.',0)
INSERT INTO @MyTable VALUES (103,'Doe','Jane','N.','Doe, John R.',0)
INSERT INTO @MyTable VALUES (104,'Doe','John','S.','Doe, John S.',0)

UPDATE MyTable SET NewMgrNum = Tbl1.EmpNum
FROM  @MyTable MyTable
INNER JOIN @MyTable Tbl1
ON LEFT(MyTable.MgrNum, CHARINDEX(',', MyTable.MgrNum)-1) = Tbl1.LName AND
   SUBSTRING(MyTable.MgrNum, CHARINDEX(',', MyTable.MgrNum)+2,500) = Tbl1.FName +' '+Tbl1.Mi

SELECT * from @MyTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

I do not need to insert any values, all the values are currently there, I just need to do the update.

Also, Middle Initial is a separate column.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

This is just testing example, you don't need to do that (declaring table variable and inserting rows), Run only query and see if it is works for you. What do you mean that MI is separate column? My table variable has that column.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

yea I see that now sorry. My bad, hehe




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Ok I get 0 results, 0 rows affected.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Ok, never mind, but how do I apply this to my table, it did in fact work.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[bborissov]

Make a good backup first, then try

UPDATE MyTable SET NewMgrNum = Tbl1.EmpNum
FROM  MyTable
INNER JOIN MyTable Tbl1
ON LEFT(MyTable.MgrNum, CHARINDEX(',', MyTable.MgrNum)-1) = Tbl1.LName AND
   SUBSTRING(MyTable.MgrNum, CHARINDEX(',', MyTable.MgrNum)+2,500) = Tbl1.FName +' '+Tbl1.Mi

Just replace MyTable with your actual Table name, also don't forget to add NewMgrNum field first.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

UPDATE usersOWPP__Merging SET NewMgrNum = usersOWPP.EmpNum
FROM  usersOWPP__Merging
INNER JOIN usersOWPP__Merging usersOWPP
ON LEFT(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)-1) = usersOWPP.LName AND
   SUBSTRING(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)+2,500) = usersOWPP.FName +' '+usersOWPP.Mi

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

I added NewMgrNum as an int with default length of 4




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

The problem is here:

LEFT(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)-1)

CharIndex returns the position within the string (in this case, the position of the comma in usersOWPP__Merging.MgrNum). Now, suppose you have a record in the database without a comma in the MgrNum column. The expression would evaluate to:
LEFT(MgrNum, -1)

You can accomodate this, though.

Change:
LEFT(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)-1) = usersOWPP.LName

To:
[tt][blue]Case When CharIndex(',',usersOWPP__Merging.MgrNum) > 0
Then LEFT(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)-1)
Else usersOWPP__Merging.MgrNum
End = usersOWPP.LName [/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Are you sure you don't have a records in usersOWPP__Merging.MgrNum that does not contai a comma in them?
What is the result of this query:

SELECT *
FROM  usersOWPP__Merging
WHERE CHARINDEX(',', usersOWPP__Merging.MgrNum) = 0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MichaelF81]

There were 2 without commas, I removed those 2 rows and now I run it with 0 errors, but

UPDATE usersOWPP__Merging SET NewMgrNum = usersOWPP.EmpNum
FROM  usersOWPP__Merging
INNER JOIN usersOWPP__Merging usersOWPP
ON LEFT(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)-1) = usersOWPP.LName AND
   SUBSTRING(usersOWPP__Merging.MgrNum, CHARINDEX(',', usersOWPP__Merging.MgrNum)+2,500) = usersOWPP.FName +' '+usersOWPP.Mi

(0 row(s) affected)

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Any ideas? I need this done kind of ASAP

Now, I get this...




"Adults are just obsolete children and the hell with them." - Dr. Seuss