Zero fill (Format) a field.

Question

Post reply

Zero fill (Format) a field.

mdkryptoking

Old Hand

Points: 301
More actions
July 30, 2009 at 6:08 am

#385087

I am creating a load that drops and creates a temp table every day. I have to do some formatting to this temp table before i can update my main table. Specifically i need to zero fill a field that comes over like this:
1111
22222
333333
4444444
55555555
so that it looks like this:
00001111
00022222
00333333
04444444
55555555
8 characters being my maximum field length. I know how to do it in access but for the life of me i cannot figure out how to do it in SQL 2008. I have tried update queries but it just tells me that format is not a valid function. So then i thought maybe i could do it in the create table step in the SSIS but i am having no luck there either. I was hoping that somebody knew how to do it in either an update query or from the create table SQL.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

CREATE TABLE #temp (test INT)

INSERT INTO #temp values (1111)

INSERT INTO #temp values (22222)

INSERT INTO #temp values (333333)

INSERT INTO #temp values (4444444)

INSERT INTO #temp values (55555555)

INSERT INTO #temp values (666)

SELECT REPLICATE('0',8-LEN(test))+CONVERT(VARCHAR,test) FROM #temp

Just replace the test column with whatever you have and change the table name.

mdkryptoking Old Hand Points: 301 More actions · Answer 2

After some experimenting i was able to use the code you provided. thank you for your help.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 3

This type of formatting shouldn't be saved in a table anywhere. First, it converts INT to VARCHAR and VARCHAR lookups are more expensive than INT lookups. Second, it will allow non-digit information to be entered into the table unless a CHECK constraint is added and that also takes extra time.

If you don't mind me asking, why is it necessary to store left-padded numeric information in a table? I mean, what are the business reasons that justify it? Thanks for any feedback here...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mdkryptoking Old Hand Points: 301 More actions · Answer 4

I think he just made some assumptions since i didnt specify data types. That field is actually a text(char) type not int. I needed to pad it as you say because it is a unique identifier in our data. If i left it without the padding half of the data would not link to our other tables and it would thus be useless. Hope this clarifies.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 5

Ack... sorry... lost track of this one. Thanks for taking the time to post the reason for this. Legacy code is always a bugger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Zero fill (Format) a field.

Cookies on SQLServerCentral