Left fill zeros - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Left fill zeros

Author

Topic

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 15:03:40

What command do I need to left fill a numerical column with zeros up to 14 digits.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-18 : 15:06:47

select right(replicate('0',14) + convert(varchar(14)@num),14)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 15:20:28

I am not sure still how to apply this, we have a table named TESTDTA.F4101 and the column named IMAITM

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-18 : 15:21:59

select right(replicate('0',14) + convert(varchar(14),IMAITM),14) From TESTDTA.F4101

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 15:30:34

This does seem to bring back the results from that column but does not fill it with zeros

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-18 : 15:43:55

you have to use an update if you want to change the column data.... are you sure you need to change the column data? whats the point, if you can format it at query time?

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 15:47:14

The query that we are running on this column tries to strip 2 leading characters that are not there. We need to fill the column in the database so we can later strip these zeros.(Long complicated story to do with UPC's and barcodes)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-18 : 15:58:34

fine.... then update:

Update TESTDTA.F4101
set IMAITM = right(replicate('0',14) + convert(varchar(14),IMAITM),14) From TESTDTA.F4101

though make sure this in a test db!!!

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 16:08:19

I ran the query and it showed that it updated the row but it still did not insert the zeros.

Thanks for all your help with this

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-18 : 16:20:48

Ah, what did they tell you at skool - always read the question!!

quote:
Originally posted by misterzr

What command do I need to left fill a numerical column with zeros up to 14 digits.

Change the column data type to a character type - varchar etc
Then rerun Coreys example

Andy

Beauty is in the eyes of the beerholder

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-18 : 16:21:41

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 16:35:23

We were going down the same road as you with the colummn properties. The trouble is the column is set to evdt_string and is being accessed by our ERP software that is writing to it. We are hesitant about changing the column properties and unsure what effect this will have on the ERP system. What do you think about changing it to varchar, running the SQL and then changing it back??

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-18 : 16:37:25

changing it back would eliminate the leading 0s. if it is a numeric field leading 0s are not stored... they are only shown by formatting the result. changing the column data type is the only option to store the leading 0s.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

misterzr
Starting Member

49 Posts

Posted - 2005-05-18 : 16:42:07

Thanks for all your help everyone, our only answer looks like manual entry.

Keith

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-18 : 16:48:42

Why not add a varchar column to the END of the table and then update that from the numeric column? Or does it have to be that specific column

Update TESTDTA.F4101
set NewVARCHARCOL = right(replicate('0',14) + convert(varchar(14),IMAITM),14) From TESTDTA.F4101

Just a suggestion

Andy

Beauty is in the eyes of the beerholder

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-18 : 17:02:00

A numeric column cannot contain character zeros whether leading or not.
What you see is the numeric converted to character for disply. The default is to start at the first non zero character or decimal point - 1.
You have a choice: change the way the column is displayed or change the way the data is stored.
Display shouldn't depend on the storage type so changing the display representation is best.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

misterzr
Starting Member

49 Posts

Posted - 2005-05-19 : 07:27:19

Thanks to all that responded, my final solution was to create another rom copy the data over too it with the zeros filled and then copy the zero-filled data back.(also ended up having to put a zero on the end)

Update TESTDTA.F4101
set IMAITM1 =(replicate('0',3)) + IMAITM + (replicate('0',1)) where len(IMAITM) = 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources