Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btamulis
Yak Posting Veteran
64 Posts |
 Posted - 2012-10-13 : 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? |
|
|
btamulis
Yak Posting Veteran
64 Posts |
Posted - 2012-10-13 : 18:26:15
|
| Thanks for reading........I ended up fixing my insert statement. I added ' before and after the ITEMNMBR which allowed SQL to insert the leading zero...Thanks againCase closed........ |
 |
|
|
|
|
|
|
|
Help Update Statement