-4

I need query for auto increment in string value-

I need XS000001,XS000009....XS000099...XS000999,XS009999,XS099999,XS999999

Please help me...

Ravindra Bisht
  • 65
  • 1
  • 8
  • further, I working on windows platform, using windows form application, I have one Addnew Insured button when I click on that it must create id between these range automatically. Thanks ... – Ravindra Bisht Oct 17 '15 at 06:11
  • If you are in windows app, why you want to handle in query.Store the latest value (say XS000010) then 'add 1 to 000010' and concatenate with 'XS' – Subin Chalil Oct 17 '15 at 06:32
  • http://stackoverflow.com/questions/14434132/how-to-make-string-auto-increment – Kishore Sahasranaman Oct 17 '15 at 07:10

1 Answers1

2

If this is for SQL Server (you're not clear on which actual database you're using), the best solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.YourTable
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   AutoIncID AS 'XS' + RIGHT('000000' + CAST(ID AS VARCHAR(6)), 6) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into YourTable without specifying values for ID or AutoIncID:

INSERT INTO dbo.YourTable(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and AutoIncID will contain values like XS000001, XS000002,...... and so on - automatically, safely, reliably, no duplicates.

But the question really is: what if your number range is used up? What do you do after have handed out XS999999 ? We cannot answer that for you .....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @RavindraBisht: if this answer helped you solve your problem, then please [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Oct 17 '15 at 11:32