Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Update Script for Between Product Codes

Posted on 2016-07-27
4
Medium Priority
?
49 Views
Last Modified: 2016-07-28
Hello,

I need a SQL Script to update my Products Table.

I have Product Code & Barcodes in the Products Table like:
Product Code  Barcode
ABC                   123456789

I need to put a zero in front of the number so:
Product Code  Barcode
ABC                   0123456789

Can someone please provide an update script for between a product code range to add the zero in front of the barcode number?

My Table is called: Products
My Fields are called: ProductCode & ProductBarcode

Thanks.
0
Comment
Question by:jspc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41732421
What is the final goal? A certain length like 10 char barcodes with leading 0s?

You can do that by right('0000000000'+ Barcode, 10)

declare @sampledata as table (barcode varchar(10));
insert into @sampledata values ('123'),('4567'),('987654321');

Select right('0000000000'+barcode,10) as paddedbarcode from @sampledata;
or
Select FORMAT(try_convert(int,barcode), 'd10') as paddedbarcode from @sampledata;

Open in new window


Once you're happy with the result the update is

Update yourtable Set barcode = ... Where Len(barcode)<10

Open in new window

That assumes a varchar barcode field with varying length.

Bye, Olaf.
0
 

Author Comment

by:jspc
ID: 41732457
The final goal is to insert a 0 in front of the barcode.
Or in other examples it may be a 9 in front.

I want to run this script between a range of product codes though.

Length is not a concern.
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 41732465
update Products
set ProductBarcode = '0'+ProductBarcode
where ProductCode between  'ABC' and 'DEF'
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41732493
>Length is not a concern.
Well, if you want to prefix a 0 you implicitly want a +1 length. The one and the other way to describe the demand are interchangable.

Bye, Olaf.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question