Getting next sequence number in a alphanum item

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 2005 Forums

Transact-SQL (2005)

Getting next sequence number in a alphanum item

Author

Topic

jauner
Starting Member

19 Posts

Posted - 2009-10-26 : 15:44:14

I have the following sql:

select top 1 item from item where item like 'PFP%'
order by item desc

I want it to give me the highest item number. But the problem is the item number is in form PFP followed by a number. I have no idea on how to do this. If it were a straight numeric I could just do descending and then grab the top 1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-26 : 15:55:46

how about this?
select top 1 item from item where item like 'pfp%' order by replace(item,'PFP','')

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jauner
Starting Member

19 Posts

Posted - 2009-10-26 : 17:28:20

No luck. It is still not giving me the largest one. For example It will give me PFP9000 if I do descending order when the largest one should be PFP23000 for example.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-26 : 17:38:36

ok in that case cast it as INT .is it always 'numeric' field that is after PFP?
select top 1 item from item where item like 'pfp%' order by CAST(replace(item,'PFP','') AS INT)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jauner
Starting Member

19 Posts

Posted - 2009-10-27 : 13:45:10

This worked great. But now I am trying to incorporate in a stored procedure that basically you tell it what the prefix is and it will give you the highest one.


ALTER PROCEDURE dbo.GAI_GetLastItemSp
(
	 @prefix    nvarchar(20)
        ,@item      nvarchar(20) Output
)
AS

select top 1 @item = item from item where item like '@prefix%'
order by CAST(replace(item,'@prefix','') AS INT) desc 

select @item

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I am using exec dbo.GAI_GetLastItemSp 'PFP',' ' to run it but no matter how I try it I never get a value back

Any ideas?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-27 : 13:56:36

yes one idea --> try removing the single quotes around '@prefix' . time for coffee break buddy
oh yes isn't a stored procedure overkill for this why not a function? imho
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-27 : 13:59:40

cant you place a identity column in your table and create a calculated field based on it like below

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

jauner
Starting Member

19 Posts

Posted - 2009-10-27 : 14:09:19

No this is a purchased application so I cannot make any changes to the table.

jauner
Starting Member

19 Posts

Posted - 2009-10-27 : 14:11:32

I figured out I had to remove the quotes around my @ variables and works like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources