Solved

SQL Update Script for Between Product Codes

Posted on 2016-07-27
4
44 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 29

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 500 total points
ID: 41732465
update Products
set ProductBarcode = '0'+ProductBarcode
where ProductCode between  'ABC' and 'DEF'
0
 
LVL 29

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

688 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