CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Muhammad Waseem
Muhammad Waseem

Reputation: 85

How to auto generate particular value in column?

How can I auto generate particular values in a table's column? I want to auto generate values in column like "WD-2010-0001","WD-2010-0002", "WD-2010-0003"and so on. Can anyone help ??

Thanks in Advance

Upvotes: 2

Views: 456

Answers (5)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

use an expression in a computed column

IF OBJECT_ID('dbo.test2') IS NOT NULL DROP TABLE dbo.test2
CREATE TABLE dbo.test2
 (
  Id int IDENTITY,
  colLikeWd varchar(2) DEFAULT 'WD',
  colLikeYear varchar(4) DEFAULT '2010',
  colLikeAuto AS colLikeWd + '-' + colLikeYear + '-' + RIGHT('0000' + CAST(Id AS varchar(4)), 4),
  colAnother varchar(100)
  )
GO 

INSERT dbo.test2(colAnother)
VALUES('AnotherColumn')
GO 5  

SELECT *
FROM dbo.test2

See demo on SQLFiddle

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

As I said in a comment, I'd store different parts separately then format them using a computed column. This could be as simple as:

create table T (
    _ID int IDENTITY(20100001,1) not null,
    ID as 'WD-' + LEFT(CONVERT(varchar(10),_ID),4) + '-' + SUBSTRING(CONVERT(varchar(10),_ID),5,10),
    ColumnA int not null
)
go
insert into T(ColumnA) values (10)
go
select ID,ColumnA from T

Which produces:

ID                 ColumnA
------------------ -----------
WD-2010-0001       10

And the next insert would be WD-2010-0002, etc.

Upvotes: 1

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

This may help you

DECLARE @String varchar(20)='WD-2010-0001'

SELECT 'WD-2010-'+
        REPLICATE('0', 4 - LEN(REVERSE(CAST(REVERSE(LEFT(REVERSE(@String),
                  CHARINDEX('-', REVERSE(@String)) -1)) AS INT)+1 )))+ 
       CAST(CAST(REVERSE(left(REVERSE(@String), CHARINDEX('-', 
               REVERSE(@String)) -1)) AS INT)+1 AS VARCHAR(4))

Replace @String with your column name

Upvotes: 0

Kamyar
Kamyar

Reputation: 18797

I think what the OP means is not how to generate that particular values, but how to fill the columns automatically. I think using triggers is the right way to go. Something like: 

CREATE TRIGGER [Add_WD] on dbo.MyTable AFTER INSERT AS
UPDATE dbo.MyTable
SET WD = '' -- you can put your value generation logic here.
FROM Inserted i
WHERE dbo.MyTable.PrimaryId = i.PrimaryId

Upvotes: 0

roman
roman

Reputation: 117636

It's a little hard to say what you really want to do, but it could be something like

select
    'WD-20' + 
    right('00' + cast(cast(rand() * 15 as int) + 1 as nvarchar(2)), 2) + '-' +
    '000' + cast(cast(rand() * 9 as int) + 1 as nvarchar(1))

Upvotes: 0

Related Questions