Click here to Skip to main content
12,510,818 members (49,391 online)
Rate this:
 
Please Sign up or sign in to vote.
How to increment varchar in server.

For example:refid0001,refid0002,refid0003

I want to increment values automatically,when i insert records in table
Posted 28-Dec-12 1:45am
maan_k459
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Instead of using varchar you can use Int as the column datatype and apply the IDENTITY (Property)[^] which will auto increment the value for you. You can then convert this to Varchar and concatenate the required string.
Here is a sample
CREATE TABLE #tbUser
(
	UserID INT IDENTITY(1,1),
	UserName VARCHAR(100)
)
 
INSERT INTO #tbUSer
SELECT 'User1' UNION
SELECT 'User2' UNION
SELECT 'User3' UNION
SELECT 'User4' 
 

SELECT 'refid' + RIGHT(REPLICATE(0,4) + CAST(UserID AS Varchar),4) AS NewCol,* FROM #tbUser
 

DROP TABLE #tbUser
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Best way to do that, Create a function DBO.GetSrNo()
ALTER Function DBO.GetSrNo()
RETURNS nvarchar(10)
as
    Begin
        Declare @C_No as int , @val as nvarchar(5)
        SET @C_No = (Select COUNT(*)+1  from T2)
        SET @val = CONVERT(nvarchar(10), (SELECT SUBSTRING('0000',Len(@C_No),Len('0000')-Len(@C_No))) + convert (nvarchar(10),@C_No))
 
        Return @val
    End
After that create a table and set DBO.GetSrNo() function as default. So It will be fill automatically.
CREATE TABLE [dbo].[T2](
    [DOC_NO] [nvarchar](20) NULL,
    [E_NAME] [nvarchar](50) NULL
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[T2] ADD  CONSTRAINT [DF_T2_DOC_NO]  DEFAULT ([DBO].[GetSrNo]()) FOR [DOC_NO]
GO
When you insert ...
INSERT INTO T2 (E_NAME) values ('VIJAY')
INSERT INTO T2 (E_NAME) values ('VINOD')
INSERT INTO T2 (E_NAME) values ('AJAY')
Output
SELECT * FROM T2 

DOC_NO E_NAME
---------- --------
0001 VIJAY
0002 VINOD
0003 AJAY

I hope u like this ...Happy New Year ... wish u good luck .. :)

Regards,
Vijay
  Permalink  
Comments
Member 10000951 11-Jul-16 3:13am
   
Good...............,Thanks Vijay nice post,really helps for me
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Method #1
you can not auto incr varchar column
make a integer column and set it autoincr while you want to display,
use query as below...
SELECT 'refid' + RIGHT(REPLICATE(0,4-len(Id)) + convert(varchar,Id) from tblnm


------------------------------OR------------------------------


Method #2
Create functions that can increment varchar with 1

write query
--insert query
insert into tbl(id) select (select dbo.incr(max(id)) from tbl)
 
--simple select query
select dbo.incr('refid0002')
--o/p = refid0003

create function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
    DECLARE @Value varchar(100)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

Create this function also
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--select dbo.Incr('refid0002')
ALTER FUNCTION [dbo].[Incr]
(
	@a varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	select @a=val from [dbo].[ParseValues](@a,'refid')
	select @a= convert(varchar,(convert(numeric,@a)+1))
	select @a= 'refId' + replicate('0',4-len(@a)) + @a
	return @a
END

Happy Coding!
:)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100