SQL Server Performance Forum – Threads Archive

convert from char to int

I have my model# as primary key and it is a aphanumeric value.
through a atored procedure I want to create a new model #
say
Mode# model_name
r001 regular excusive
r002 regular explosive
r003 regular inclusive
c001 commercial excusive
c002 commercial inclusive
and so on looking at r/c it has to generate next maximum model# say r004,c003..
( think beyond of the case r009,r099 as well as for c009,c099)

---

What happens if you go beyond 999? Also, what happens if you have two of the same model starting with the same letter? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Below code will give you the result but it generates number only till C999. In order to make it work after that code needs to be modified by varying the size of the variable definitions. DROP TABLE #temp1
DECLARE @PREVIOUS CHAR(4) — Declaring vaiable for input parameter
SET @PREVIOUS=’C999′ — Feeding the Input variable for output SELECT SUBSTRING(@PREVIOUS,1,1) as Pre_Character, –Separating the character
(convert(char(3),CONVERT(INT,SUBSTRING(@PREVIOUS,2, DATALENGTH(@PREVIOUS)))+1)) as trail_number — Incrementing the Number
into #temp1 select pre_character+replicate(‘0’,len(@previous)-1-datalength(rtrim(trail_number)))+trail_number from #temp1 –Adding the character and the number

---

When you do this, make sure the SELECT is done at the same time as the actual INSERT (don’t select your next number and try to insert it three steps down in the process). Also make sure you have a unique constraint on this column. If you don’t do these steps, you’re going to have concurrency and big integrity issues down the road. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>