Add Zeros to a product number in SQL [duplicate]

Question

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.

HaveNoDisplayName · Accepted Answer · 2014-12-08 16:31:24Z

2

Try this, you can use this statement

RIGHT('000000000000'+ISNULL(ProductNo,''),12)

edited Dec 8, 2014 at 16:31

answered Dec 8, 2014 at 16:19

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Magnus · Accepted Answer · 2014-12-08 16:27:22Z

1

This should do it:

UPDATE tblA SET ProductNo = REPLICATE('0', 12 - LEN(ProductNo)) + ProductNo

answered Dec 8, 2014 at 16:27

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

1 Comment

Alexander P. Over a year ago

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!

Magnus · Accepted Answer · 2014-12-08 18:50:30Z

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.

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.

Collectives™ on Stack Overflow

Add Zeros to a product number in SQL [duplicate]

3 Answers 3

Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Linked

Related