3

I have two tables with product numbers. They both are limited to 12 characters (varchar(12)). One of them (product A) has a number structure like this:

Product No:

2345
568
89

And product B has the same exact numbers but with zeros to fill the 12 characters missing. It is something like this:

Product No:

000000002345
000000000568
000000000089

I just want to modify product A table to add the zeros at the beginning of the sequence. I had an idea with REPLACE() function but to add the zeros I might need another function. Thanks for reading and sorry for the time.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alexander P.
  • 382
  • 1
  • 7
  • 19

3 Answers3

2

Try this, you can use this statement

RIGHT('000000000000'+ISNULL(ProductNo,''),12)
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
1

This should do it:

UPDATE tblA
SET ProductNo = REPLICATE('0', 12 - LEN(ProductNo)) + ProductNo
Magnus
  • 45,362
  • 8
  • 80
  • 118
  • The option with the RIGHT function also works but for me this is the cleanest way to do it. I liked it, thanks a lot! – Alexander P. Dec 09 '14 at 13:38
0

I just want to modify product A table to add the zeros at the beginning of the sequence.

Big hairy question for you: Are you absolutely certain that you want to store these values in the table with leading zeros, or just be able to display it as-needed with leading zeros?

Reason I ask is because the varchar(12) implies 13 bytes in memory, where an int only takes 4, which will make a big difference if this column participates in indexes and foreign key relationships with other tables.

Magnus
  • 45,362
  • 8
  • 80
  • 118
Jim Horn
  • 879
  • 6
  • 14
  • Which is bad because the moment someone starts to add characters to what incidentally is a character based identification code your saving comes and hits you into your backside with a lot of force. NEVER do that type of mistake. Use an internal int as number, then put a unique index on the varchar identification code. Most people learned this lesson of not smart savings in the year 2000 problem. – TomTom Dec 08 '14 at 18:22