Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UPDATE field to add preceding 0's 2

Status
Not open for further replies.
cdck

cdck

Programmer
Nov 25, 2003
281
US
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
 
Sort by date Sort by votes
Code: 
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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...

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

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
A quote from my associate:

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

Thank you!

Cheryl dc Kern
 
Upvote 0 Downvote
an alternative version
Code: 
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
464
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
521
BobSuruncle
BobSuruncle
Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
320
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
800
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
515
Volvere
V

Part and Inventory Search

Sponsor

Back
Top