|
btamulis
Yak Posting Veteran
USA
63 Posts |
 Posted - 10/13/2012 : 17:29:04
|
I have a table with 3418 records.
The table has a column for ITEMNMBR. The ITEMNMBR should have 12 characters.
In my table, I have 3157 records where the ITEMNMBR has only 11 characters. Basically my text file (used to insert records) 'dropped' a leading zero for the ITEMNMBR.
Here a sample of what's in table:
31568486333
31568486340
31568486364
43442005561
43442015256
43442015560
43442025255
43442025569
43442035568
All these values need to be updated to be:
031568486333
031568486340
031568486364
043442005561
043442015256
043442015560
043442025255
043442025569
043442035568
Basically I want an update statement that will look at the value in the ITEMNMBR column - if it has 11 characters - add '0' (to the first space) if it has 12 characters - leave it alone.
Here's an example of my insert statement:
INSERT INTO [RBI].[dbo].[IV10303]
([STCKCNTID]
,[ITEMNMBR]
,[LOCNCODE]
,[BINNMBR]
,[UOFM]
,[QTYBSUOM]
,[COUNTEDQTY]
,[DECPLQTY])
VALUES
('OCTOBER2012'
,031568486340
,02
,'BINNMBR'
,'EACH'
,1
,1
,1)
When run - this inserted '31568486340' - and yes my insert came from Word - into NotePad - then copied into query window. Visually the leading zero is visible - but obviously SQL didn't see it. I'm wondering if I have a quick update fix or if I have to start over...
Any thoughts? |
|
Help Update Statement
