Trying to Truncate First Digit in Five Digit Value

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to Truncate First Digit in Five Digit Value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-25 : 10:24:01

Mike writes "First of all, I am running MS SQL 2000 on Windows Server 2003. I have what I think is an odd one here. I have a table "items" in my database that contains transaction information. Each row in the table contains, amongst other things, a run, batch and sequence number for each item captured.

There are several batches in this table where an item sequence number rolled over 10000, when it should have stopped at 9999. My solution for the data already captured this way is to assign items to a new batch and then drop the leading character (seq number 11500 would become 1500). There are a good deal of items involved here.

I can easily assign the new batch number to each item by performing the following:

update dbo.items set batch= 201 where date= '08/08/06' and run= 22 and batch= 01

What I want to know is, how - if at all - can I go about changing seq numbers 10010-12250 to 0010-2250 in one step? I don't want to have to go into each record and manually change the sequence number but I don't have the foggiest idea how to go about this.

Any thoughts?"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-25 : 10:34:35

Maybe this...?


Declare @t table (seq int)
Insert Into @t 
Select 1361 Union All 
Select 6346 Union All 
Select 9874 Union All 
Select 6155 Union All 
Select 16544 Union All 
Select 15648 Union All 
Select 19873 Union All 
Select 11564

Select 
	seq,
	newSeq = seq % 10000
From @t order by 1

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-25 : 10:35:38

If your sequence column is integer, you can try this

update tbl
set Seq = Seq % 10000
where Seq > 9999

If your sequence column is varchar, simply use Right()

update tbl
set Seq = Right(Seq,4)

Harsh Athalye
India.
"Nothing is Impossible"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-25 : 10:37:00

Or if its a varchar column...


Declare @t table (seq varchar(10))
Insert Into @t 
Select '1361' Union All 
Select '6346' Union All 
Select '9874' Union All 
Select '6155' Union All 
Select '16544' Union All 
Select '15648' Union All 
Select '19873' Union All 
Select '11564'

Select 
	seq,
	newSeq = Right(seq,4)
From @t order by 1

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources