Sql concatenate problem?

Question

In my table i have a column called Dep_user_code which is nothing but employeeid...Everytime I need to increment the employeeid when i insert a new value..but it has both alphabet and number..i need to increment the number alone.. for example if my employeeid is 'NECUSER0001' means next time when i insert a new employeeid it has to be 'NECUSER0002' dynamically i have to generate like this..everytime when i insert a value it has to increment..

I have tried like taking the string part and number part like this but dont know how to implement this...Any suggestion?

select SUBSTRING(Dep_user_code,1,7)  from NEC_Customer_User_Map
select SUBSTRING(Dep_user_code,8,4)  from NEC_Customer_User_Map

Answer 1

you should also keep an identity key. use SELECT IDENT_CURRENT('NEC_Customer_User_Map') to find out last inserted ID.

Answer 2

If the value is always text then numbers, you split apart the value using Patindex:

Select Substring( Dep_user_code, 1, PatIndex( '%[0-9]%', Dep_user_code) - 1 ) As TextPortion
    , Substring( Dep_user_code, PatIndex( '%[0-9]%', Dep_user_code)
            , Len(Dep_user_code) ) As NumberPortion

However, whether you can use an identity in combination with a prefix depends on whether you can allow gaps. If you cannot allow gaps, then you need to query for the next id value that you can use which can be done in a variety of ways depending on the needs.

Answer 3

I've had to support databases with setups like this before and while I'm generally not a fan of this style, I'm assuming you have some reason for not storing the NECUSER in one column and the incrementing identity integer in another column with the PK set to both. If not, I'd suggest going that route and letting SQL do the work for you.

Otherwise, using the result of the following query should yield the results you want. I've added comments to try and answer any questions the query might raise.

SELECT SUBSTRING(Dep_user_code, 1, 7) + 
    RIGHT(
        REPLICATE('0', 3) +   --Ensure we have padding 0s
        IsNull(MAX(CAST(SUBSTRING(Dep_user_code, 8, 4) AS INT), -1) + 1  --Work with ints, find MAX or set NULL to -1 so +1 will = 0
    , 4)  --Only want 4 character total from RIGHT function
FROM NEC_Customer_User_Map

Answer 4

WITH last AS (
  SELECT MAX(Dep_user_code) AS Code
  FROM NEC_Customer_User_Map
  WHERE LEFT(Dep_user_code, 7) = 'NECUSER'
)
SELECT LEFT(Dep_user_code, 7) + RIGHT(CAST(STUFF(Code, 1, 7, '1') AS int) + 1, 4)
FROM last

The RIGHT part does the following:

• replaces 'NECUSER' with '1' thus getting something like '10002';
• casts the result as int;
• increments by 1;
• (implicitly) casts the value to varchar and gets the last 4 chars.

Maybe STUFF(Code, 1, 7, '1') should better be replaced with '1' + RIGHT(Code, 4), not sure.

EDIT: As it happens, the implicit conversion could also be employed in case of converting the string to the integer too:

... + RIGHT(STUFF(Code, 1, 7, '1') + 1, 4) ...

or

... + RIGHT('1' + RIGHT(Code, 4) + 1, 4) ...

Answer 5

 declare @max varchar(20)
 declare @number varchar(20)
 select @max =  max(cast(substring(dep_user_name , 8, 4) as int))  from NEC_Customer_User_Map (nolock)
 select @max = isnull(@max, 0) + 1
 select @max = (case     when len(@max) = 1 then '000' + @max
                         when len(@max) = 2 then '00' + @max
                         when len(@max) = 3 then '0' + @max
                         else @max 
                end)
 Select @number = (Substring( dep_user_name, 1, PatIndex( '%[0-9]%', dep_user_name) - 1 ) +   @max)  from NEC_Customer_User_Map                      
 insert into NEC_Customer_User_Map(Dep_User_Name)  values (@number )

Answer 6

You can consider to have both parts of Dep_user_code as separate fileds in your db in order to take advantage of several tsql features like IDENTITY and IDENT_CURRENT()