MS SQL Server/separating integer values from string variables.
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
The mechanics of this are relatively easy. The tricky part can creep in depending on how many instances of this process will run simultaneously. You have to implement measures to ensure that 2 different processes don't both think they have BTBST0027, for example, because they ran at almost the same instant and the 1st didn't get that ID "reserved" before the 2nd queried for the most recent ID.
For now, I'll assume that all of that concurrency is NOT a problem owning to the configuration of your process. If it is an issue, let me know and we'll explore further.
Other assumptions, based on your example:
* the 1st 5 characters of the ID are letters
* the numeric portion of the code will be 4 digits.
(Of course, if the above is true you should change your ID to NCHAR(9) instead of NVARCHAR, because character fields generally perform better when indexed... you DO have an index on UserID, right?)
* It is known ahead of time what "area code" the to-be-entered person has
Anyway, assuming all of that, the code to do what you want is relatively straightforward:
DECLARE @AreaCode CHAR(5) -- "area code" of the person to be entered. Probably a parameter if this becomes a stored proc
DECLALE @ToBeAddedID NVARCHAR(9) --not sure about the right declaration for this. should match column.
SELECT @ToBeAddedID =
@AreaCode + RIGHT('0000' + CAST(CAST(RIGHT(MAX(UniqueID),4) AS INT) + 1 AS VARCHAR(4)), 4)
FROM SomeTable WHERE UniqueID LIKE @AreaCode + '%'
--Note: I'm doing this while in SSMS. I'm not 100% sure the "LIKE @AreaCode + '%'" will work. If not you'll
--have to build another variable @AreaCodeSearch CHAR(6) and populate it with @AreaCode + '%'
@ToBeAddedID now contains the next numerical sequence for @AreaCode
I'm sure there will be followups, so feel free. Also, tell me what version of SQL Server you're using