Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Update Script for Between Product Codes

Posted on 2016-07-27
4
Medium Priority
?
51 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
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 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