How to change existing data in table

[marsbar105]

I am trying to change some the format of the data within a table. Here is the example:

I would like to change a series of numbers ( 149 ) to this new format
( 00149 ). Is there a way to do this without going through thousands of records???

Maybe with a query or something??

Thanks for your help,

Marsbar

[JBane]

Mars,
The leading zeros will not be preserved unless the column has some sort of character datatype. If that is the case then:

update table
set column = '00' + column


Jeff

[marsbar105]

What is the data was shorter like 40 and I wanted to fill in the extra space with 00040??

I guess what I am trying to say is that I have random numbers from 1 to 10589. I would like all numbers to follow the same format "00000". Now none of them are 5 digits long as it stands, so how do I add missing digits to numbers that are shorter than 5. Like 149 or 51 or 4....??

Thanks a bunch,

Mars

[marsbar105]

The datatype is text, because some of the numbers have letters at the end like A - B - C etc.


Mars

[GaryG]

Mars,

I replied to your other thread, but now that I read this one I see your data is text not numeric. The idea is similiar:
write a function that takes one parameter -- the field containing the values you want changed. The function should return a string. The function should: deal with NULL values as well as regular strings. Count the length of the string passed in and prefix it with the appropriate number of zeros, and use the new string as the function's return value. You can then use the function in a update SQL statement, e.g.:

  update X
  set f = myFunction(f)

where X is the table name & f is the field you're updating.
That would do it.

Cheers,
Gary