question

skpani avatar image
skpani asked

Auto increment for a Varchar Column

At below I have written a script that will create a table Test and will insert 100 values. CREATE TABLE dbo.Test(PRODID INT,PRODNAME varchar(30)) declare @PRODID int=1 while @PRODID resultsinline-table-valued-function
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Will the alphabetic prefix increment as well? e.g. PR999, PS000
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered
> @skpani wrote: > At below I have written a script that will create a table Test and will insert 100 values. > The following will produce exactly the table and table contents that you asked for and is very similar to what Father Jack first posted on this thread except that it also makes the table on-the-fly and it uses the "PR" prefix that you wanted. I realize that it's only for 999 rows but it will do so in a very high speed, set based manner rather than using any type of explicit loop or the slowness of an rCTE that counts. SELECT TOP (999) ProdID = CAST('PR'+RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(3)),3) AS VARCHAR(30)) , ProdName = 'Thread' INTO dbo.Test FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ; If what you really wanted is a method for the table to automatically increment the ProdID column, there are several methods available depending on how you want it done. The easiest method is to simply make a calculated column. This method was first suggested on this thread by Mister Magoo but you needed to make the realization that you needed to change the formula a bit to get exactly what you wanted. Here's the table code for that method. It uses what is known as a "calculated" column in conjunction with an IDENTITY column. CREATE TABLE dbo.Test ( ProdID AS CAST('PR'+RIGHT('000'+CAST(RowNum AS VARCHAR(3)),3) AS VARCHAR(30)) PERSISTED ,ProdName VARCHAR(30) ,RowNum INT IDENTITY(1,1) CHECK (RowNum
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bhanupratapsngh9 avatar image
bhanupratapsngh9 answered
CREATE TABLE dbo.Test(PRODID INT,PRODNAME varchar(30)) declare @PRODID int=1 while @PRODID<= 100 begin insert dbo.Test values(@PRODID,'THREAD'**+CAST(@PRODID as varchar)**) set @PRODID+=1 end select * from dbo.Test drop table dbo.Test
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@bhanupratapsngh9 I think you haven't got my question properly.I know what you have done but PRODNAME is dependent on PRODID which I don't want be.Ok just omit the PRODNAME column means we have only PRODID column.Increment this column.My output must be like this:'PR001' 'PR001', 'PR003
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
I think you are explaining that you need something like this: USE [tempdb] GO DECLARE @Table TABLE (ProdID varchar(5), ProdName varchar(100)) INSERT @Table ([ProdID], [ProdName]) SELECT TOP 200 RIGHT(REPLICATE('0', 5) + CAST(ROW_NUMBER() OVER (ORDER BY [C].[object_id]) AS varchar(10)), 5) AS Prod_ID, 'THREAD' FROM [sys].[columns] AS C SELECT * FROM @Table AS T In this code we concatenate a fixed string of '00000' to the ID and then use the RIGHT function to return a string that is the required ID plus a prefix of the desired number of '000'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image
Mister Magoo answered
Here is a sample table definition that includes an incrementing char primary key. set QUOTED_IDENTIFIER on; create table IncrementingChar ( ident int identity(0,1) not null, ident_char as (char(65+((ident/26000) % 26))+char(65+((ident/1000) % 26))+RIGHT('000'+CAST(ident as varchar(1000)),3)) persisted primary key );
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

skpani avatar image
skpani answered
Thanks to all for your valuable answers. @Fatherjack and @Jeff Moden hat's off to you both .
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks you very much for the feedback. It's much appreciated.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.