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
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.