UPDATE field to add preceding 0's 2

[cdck]

I need to run an update on one numeric field in the database so that all entries have the same number of digits. Currently, while most have 10 digits, with preceding zeros to fill the extra spaces, about 1/3 have no preceding spaces, which causes them to sort incorrectly. How can I add zeros before the number until the number has the same number of digits as the majority have?

I am assuming that it would be something like:

UPDATE Table
SET field = '00000' & field
WHERE field = '--'

using the underscore wildcard for the existing digits. I would then have to run the update once for every length of number (ie, once for those entries that have 3 characters, then those that have 2, then those that have 1, etc.). This would not be an annoyance, because I only have to do this once to make some new imported data align with the existing structure.

Am I on the right track, or is there a simpler way?

Cheryl dc Kern

 

[gmmastros]

UPDATE Table
SET field = Right('0000000000' + field, 10)

Note that there are 10 0's in the string.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

if it is indeed a numeric column as you indicated in your first post, you can update it all you want, the leading zeroes will be ignored

however, you can always use the expression Right('0000000000'+field,10) when SELECTing from the table

r937.com | rudy.ca

 

[cdck]

Thank you both for the answer! I must be mistaken about the field being numeric, as most of the fields do have the preceding zeros - I'm assisting someone else who formatted the table.

Cheryl dc Kern

 

[cdck]

I have verified that it is a character field, with 6 characters. When we ran the query, it stated that the rows were all affected, but when we opened the table, there were still no preceding zeros. Here is the code exactly as we used it:

UPDATE inboms
SET fitem = RIGHT('000000' + fitem, 6)

Any more suggestions?

In case it helps, this is SQL Server 2000.

Cheryl dc Kern

 

[gmmastros]

That happened because your data type is Char(6). If it was varchar(6), then the code would have worked. Char field differ from varchar in that they are padded with spaces at the end of the data. varchars are not padded with spaces. RTrim will remove the padding.

Anyway...

UPDATE inboms
SET fitem = RIGHT('000000' + [!]RTrim([/!]fitem[!])[/!], 6)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cdck]

A quote from my associate:

"Save that one. George is the freakin man."

Thank you!

Cheryl dc Kern

 

[SQLSister]

an alternative version

UPDATE inboms
SET fitem = RIGHT(replicate(0,6)+ RTrim(fitem), 6)

I find it handy if I want to add a lot of zeros, so I don't mistype the number of 0s I wanted.

Questions about posting. See faq183-874