Reputation: 85
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
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
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
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
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
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