plus 1 to key

vbnetcoder
vbnetcoder used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Author

Commented:
ty

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial