SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help Update Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

btamulis
Yak Posting Veteran

USA
64 Posts

Posted - 10/13/2012 :  17:29:04  Show Profile  Reply with Quote
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

USA
64 Posts

Posted - 10/13/2012 :  18:26:15  Show Profile  Reply with Quote
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 again

Case closed........
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000