Select max() Function - 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

SQL Server Development (2000)

Select max() Function

Author

Topic

hemulll
Starting Member

9 Posts

Posted - 2006-06-16 : 09:57:23

Hello All.
I have a varchar column with data, for example:
id
---
ov1
ov2
ov3
---
i want to select this data and do +1 to last number.
I use select max(id) function , for example:
declare @temp int
select @temp = convert (int,(substring (max(id),3, len(max(id))))) +1
from TB_TICKETS
where id like 'ov%'
print @temp

-----
ov4

and after this result i do insert to the column id
this function work ,but after ov9 that function Stop.
it can't do +1 to ov10 and every time is remaining ov10.
Anyone can help me with this issue ?
Sorry for My English .

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-16 : 10:04:37

[code]select @temp = ~~convert (int,(substring (max(id),3, len(max(id))))) +1~~
max(cast(substring(id, 3, 10) as int)) + 1
from TB_TICKETS
where id like 'ov%'[/code]

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 10:05:54

-- prepare test data
declare @test table (t varchar(50))

insert @test
select 'ov1' union all
select 'ov2' union all
select 'ov4' union all
select 'ov31' union all
select 'ssss1' union all
select 'ssss99'

-- show data before inserting new values
select * from @test

-- do the work for series OV tickets
declare @prefix varchar(50)

select @prefix = 'ov'-- update OV serial

insert @test
select	@prefix + convert(varchar, t.n + 1)
from	(
		select	max(convert(int, substring(t, 1 + len(@prefix), 50))) n
		from	@test
		where	t like @prefix + '%'
	) t

-- do the work for series SSSS tickets
select @prefix = 'ssss'-- update SSSS serial

insert @test
select	@prefix + convert(varchar, t.n + 1)
from	(
		select	max(convert(int, substring(t, 1 + len(@prefix), 50))) n
		from	@test
		where	t like @prefix + '%'
	) t

-- show data after inserting new values
select * from @test

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 10:16:36

Also refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

hemulll
Starting Member

9 Posts

Posted - 2006-06-16 : 10:27:48

Thank You RyanRandall
This is a Very Good Solution for me .
Thanks

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-16 : 10:55:21

hemulll - You should give the other guys some credit too

Peso's suggestion is more generic, and madhivanan's link gives you some more things to think about...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 14:09:58

quote:
Originally posted by RyanRandall

hemulll - You should give the other guys some credit too

Ryan, it is ok. I am used to that

I discovered three bugs with our solutions. The first bug is that prefix were ov3 then the function most likely would found 9 as max integer. Then the function would add 1 to 9 ( = 10 ) and concatenate to ov3, producing ov310 as next ticket serial! Wow...
The second was that if prefix was only 'o', a cast error would occur. The third bug is that if prefix were not found at all, NULL were inserted.

You said you wanted iron clad?

-- prepare test data
declare @test table (t varchar(50))

insert @test
select 'ov1' union all
select 'ov2' union all
select 'ov4' union all
select 'ov31' union all
select 'ssss1' union all
select 'ssss99'

-- show data before inserting new values
select * from @test

-- do the work
declare @prefix varchar(50)

select @prefix = 'ov' -- Throw anything in!

insert @test
select	@prefix + convert(varchar, t.n + 1)
from	(
		select	max(convert(int, substring(t, 1 + len(@prefix), 50))) n
		from	@test
		where	t like @prefix + '%'
			and not substring(t, 1 + len(@prefix), 50) like '%[^0-9]%'
	) t
where	not @prefix like '%[0-9]%'
	and not t.n like '%[^0-9]%'

-- show data after inserting new values
select * from @test

Peter Larsson
Helsingborg, Sweden

hemulll
Starting Member

9 Posts

Posted - 2006-06-17 : 03:44:04

I'm Very Sorry for this incident
Thanks Guys

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-06-18 : 19:57:41

Does any of this actually work when there are simultaneous users? How do you get around the fact that each transaction has it's own idea of what max() will be? Surely you're better off using an identity and add the 'ov' when you select the value (or create a view that does).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-19 : 00:54:24

I don't see why it shouldn't.
Wrap a loop around and filter "where new value not previously present in table".

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources