plus 1 to key

I have a field like this with values like this:  NJ001013205.  there are all the same length starting with NJ and then the numbers.  how would I get the mad field + 1

so, if the max value was this  NJ001013205 I would return  NJ001013206
vbnetcoderAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
How about this on SQL Server 2012+.

SELECT LEFT(MAX(Field1), 2)+FORMAT(CAST(RIGHT(MAX(Field1), 9) AS INTEGER)+1, '000000000') AS nextField1 FROM Table1;

Open in new window

And in older versions something like:

SELECT LEFT(MAX(Field1), 2)+RIGHT('000000000'+CAST(CAST(RIGHT(MAX(Field1), 9) AS INTEGER)+1 AS VARCHAR(9)),9) AS nextField1 FROM Table1;

Open in new window


»bp
0
 
Pawan KumarDatabase ExpertCommented:
You can use below -

DECLARE @rt AS VARCHAR(100) = 'NJ001013219'
SELECT 

	CASE WHEN CAST( RIGHT(@rt,1) AS INT ) < 9
		
		THEN SUBSTRING(@rt,0,LEN(@rt)) + CAST( CAST( RIGHT(@rt,1) AS INT ) + 1 AS VARCHAR)

	ELSE
			
		SUBSTRING(@rt,0,LEN(@rt)-1) + CAST( CAST( RIGHT(@rt,2) AS INT ) + 1 AS VARCHAR)

	END

Open in new window


Output

(No column name)
NJ001013220

Open in new window

0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.