incrementing a string - SQL Server Forums

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

Transact-SQL (2000)

incrementing a string

Author

Topic

janani
Starting Member

2 Posts

Posted - 2004-07-13 : 11:42:07

Hi,

I have a string of this format 04-1234 where 04 is the year and 1234 is the number assigned to that record. This value will be stored as a varchar. I need to increment this value by choosing the highest existing value from the table and incrementing it.Can any1 tell me how to do it?

Kristen
Test

22859 Posts

Posted - 2004-07-13 : 11:52:57

[code]
SELECT RIGHT(CONVERT(varchar(10), DATEPART(Year, GetDate())), 2)
+ '-'
+ CONVERT(varchar(10), CONVERT(int, MAX(RIGHT(MyColumn, 4)))+1)
FROM MyTable
[/code]
This assumes that the right part is independant of the left part in terms of finding the highest value - i.e. the right part does NOT start again at 0000 when the year changes

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-13 : 12:02:47

OR----
create table #test(Val1 VARCHAR(10))
insert into #test values('04-1234')
insert into #test values('04-10000')

declare @Num varchar(10)

set @Num = (select num from
(select top 1 right(replicate('0', 10) + right(val1, len(val1) - patindex('%-%', val1)), 10) as num
from #test
order by 1 desc) as a)

select CAST(@Num as INT) + 1

THIS Should be in the transact sql forum though

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-13 : 13:24:27

Get away with you, '04-10000' isn't "a string of this format 04-1234". Oh no, that would be a variation from the specification that we would gladly charge the Erstwhile Client extra for!

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-13 : 13:28:49

I catered for more than 10 000 "whatevers" for the year for the sake of that guy called Justin Case.

Also to illustrate that in a varchar order 1234 is > 10000

Duane.

janani
Starting Member

2 Posts

Posted - 2004-07-22 : 14:21:18

well the right part is dependent on the left side. the right part starts at 0001 when the year changes. how can i modify the code for this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 14:23:27

Article Discussion forum for this question? Don't think so.

Tara

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-23 : 00:00:55

Yeah! This should probably be in the transact sql forum.
Anyways - This should do it then:

create table #test(Val1 VARCHAR(10))
insert into #test values('04-1234')
insert into #test values('04-10000')
insert into #test values('03-16660')
insert into #test values('04-9999')
insert into #test values('02-10000')

declare @Num varchar(10)
declare @Year varchar(2)

set @Year = (select max(left(val1, 2)) from #test)
set @Num = (select num from
(select top 1 right(replicate('0', 10) + right(val1, len(val1) - patindex('%-%', val1)), 10) as num
from #test
where left(val1, 2) = @year
order by 1 desc) as a)

select CAST(@Num as INT) + 1

Duane.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-07-23 : 13:10:57

I agree :) T-SQL Forum it is.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources