Incrementing string values
-
Hi all. (SQL Server 2000)
Can someone please help me with the following problem: -
I have a job reference number column in my table varchar(20) and I need to get the next number in the sequence.
The user may have already set up the following records: -
1abd3d1001
1abc3d1002
1abc3d1003
In this case, the system would need to return 1abc3d1004.
The user may also be using another sequence as follows: -
55dcbaaaz997
55dcbaaaz998
55dcbaaaz999
In this case I would need to return 55dcbaaaz1000
The user could also be using a third sequence eg: -
aaaa55bb1
aaaa55bb2
aaaa55bb3
aaaa55bb4
In this case I would need to return aaaa55bb5 as the next value to the user.
Basically I will pass to the SP the first part of the sequence eg in the last example, aaaa55bb The system then needs to get the highest value with this sequence and increment by 1. Note that the highest value found must contain numbers after the sequence. For example, If the highest value found was aaaa55bb123d123, this would not be valid because it is using a different sequence. Eg aaaa55bb123d as the string part as opposed to just aaaa55bb.
Can anyone help me come up with an SP to do this.
Thanks in advance.
MB.
-
Declare @first_part varchar(20),
@len_first_part int
Set @len_first_part = Len(@first_part)
Select max(seq_num)
From (Select seq_num = Right(your_col, Len(your_col - @len_first_part)
From Your_Table
Where Left(your_col, @len_first_part) = @first_part
And Isnumeric(Right(your_col, Len(your_col - @len_first_part)) = 1
) as dt
I haven't tested the above and I'm sure there must be a more efficient way but this should give you a good idea.
-
Actually the above sql will work but needs the following changes:
Select max(seq_num) + 1
From (Select seq_num = Right(your_col, Len(your_col) - @len_first_part)
From Your_Table
Where Left(your_col, @len_first_part) = @first_part
And Isnumeric(Right(your_col, Len(your_col) - @len_first_part)) = 1
) as dt
That should do it - Added a 1 because the SP needs to return the next number in the sequence and there were some closing parens that were included in the Len(Your_Col) portion...
-
Hi, I had already made those changes to get it to work but thanks anyway (to both of you).Regards.
Mark.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply