Zero fill

[Palmyra]

I have a table with an varchar field (box number) populated with numbers that are zero-filled to ten characters in each field, e.g.:

0016817725
0117995705

I have another table with a varchar field that I have to compare to the field above. It's not zero filled and the data can be of any length. Can I somehow in a query do a zerofill to ten in this table's field for comparison purposes, but not alter the actual data.

Any help appreciated.

 

[SQLBill]

It might be easier to do a CONVERT in your query. Let us say TABLEA has the full 10 digit value and TABLEB has the value without leading 0's. All you need to do is CONVERT(INT, <TABLEA's column>) and compare that to the TABLEB number column. Converting the value with leading zeros to INT will drop the leading zeros. Since you aren't UPDATEing, you aren't changing the data.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SgtJarrow]

You should be able to use the Right function for the non-zero side...

Right('0000000000' + FIELDNAME, 10)

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[Crowley16]

that's an interesting way to do padding, I usually have to resort to replicate and len...

--------------------
Procrastinate Now!

 

[djj55]

Crowley16, Robert's way is how I do it. Just goes to show there are various ways to get to the same end.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!