You are here:

MS SQL Server/Adding 1 to a NVARCHAR variable



I have a list of userIds that are nvarchar characters. They are a mix of numbers and letters usually something along the lines of:




The letters represent the area of the UK where the person is from.

What I would like to do is to write a query that will select the latest userID from a specific location and add 1 to it. For example, using one of the userIDs above:

So the last person who was entered into the database had the userID: BTBST0026, so the new person who is about to be entered will be assigned the userID - BTBST0027

so the query will have to find the latest userID by somehow looking at the numbers at the end of the userID (in the case above 0026) and then add 1 to it and then combine it back with the letters.

Is this possible to do in a SQL Server stored procedure? If so, how do I do it.

Thank you for your help

Dear Tejus,

Apologies for late reply!

Here are some steps to help you get next User ID:
1. Get numeric component of user id such as "0026". RIGHT('BTBST0026', 4) can give that.
2. Convert this to numbers only: CAST('0026') AS INT should do that
3. Add 1 to the number: 26 + 1 will give that
4. Convert number to text by adding left out zeros: LEFT('0000', 4 - LEN(CAST(27 AS VARCHAR(4))) + CAST(27 AS VARCHAR(4)) should give that
5.Thatís the new ID you wanted

Fee free to follow-up.


MS SQL Server

All Answers

Answers by Expert:

Ask Experts


Jwalant Natvarlal Soneji


From general queries about SQL Severs both 2000 and 2005 to advanced features like Integration Services and Reporting Services.


Experience in the area: I have been working with database triggers, stored procedures, views, function, joins, dts, Query optimization. Education/Credentials: BE IT - 2005 Batch with First Class.


BE IT, India

Awards and Honors

©2017 All rights reserved.

[an error occurred while processing this directive]