Any way to remove a leading zero from a certain field in every record?

[BTilson]

I have to import daily payroll hours reports from the payroll department into my database every day in order to calculate each employees efficiency for the previous day. The trouble is that in my database, employee ID numbers are 3 digit "strings." Ex: 020, 041, 116, 291, etc etc... But the report from payroll has them stored as 4 digit strings. Ex: 0020, 0041, 0116, 0291. Is there any way other than simply changing each record manually to take off the leading zero on all the 4 digit employee ID codes?

Thanks! Any help is appreciated.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[r937]

i wouldn't bother changing the data that comes in daily, i would simply do the join like this --

select foo
     , bar
  from employees
inner
  join payroll
    on employees.id
     = substring(payroll.id,2,3)

rudy

http://r937.com/

SQL Consulting

 

[r937]

oops, microsoft access

replace substring(payroll,2,3) with mid(payroll,2,3)

rudy

http://r937.com/

SQL Consulting

 

[nice95gle]

Build a query from the payroll table then put the below expression in your query. I assume you know where to put it.

Right([Employee ID],3)

Good Luck

 

[Arafel]

Hi,

Great answers, I must remember them!

You might want to update your database to 4 digit as the payroll dept are obviously anticipating employee numbers exceeding 999, at some point.