Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding leading zero's

Status
Not open for further replies.

luvmy2Js

IS-IT--Management
Sep 1, 2010
43
US
I have a file that I will be importing into the database as updates are being made. The issue is the column "action" is as follows:
1,4,10 etc and when I join to "reasons" table the columns is as follows:

01, 04, 10 so therefore I am not picking up the value from the action table becasue the numbers aren't the same number of characters when it is a single digit.

Here is my join:

s.action = p0.action
-- is there a way I can correct the field in the join or do I need to do an update when importing the file in?

Thanks,

Fatisha
 
Code:
RIGHT('000000000000'+CAST(YourNumber as varchar(????), ????)
Where [????] is the number of leading zeroes you want.
In short you convert the number to something like this:
'000000000123' and get as many chars as you want from the right of the string.
So if you want to convert the number up to 5 chars with leading zeros:
Code:
SELECT RIGHT('00000'+CAST(123 as varchar(5)), 5),
       RIGHT('00000'+CAST(1 as varchar(5)), 5)
       RIGHT('00000'+CAST(1234 as varchar(5)), 5),
       RIGHT('00000'+CAST(12345 as varchar(5)), 5)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
What data type are both columns? You might be comparing an INT column with a VARCHAR or CHAR column. If that is the case, you could CAST the VARCHAR/CHAR column to be INT.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top