CODEWITHSUNDEEP
sql-server
LCJ
LCJ

Reputation: 22652

How to specify max length for a computed column

I am adding a computed column from three fields. It will be a VARCHAR field. I need to specify the max length for the computed column. How can we specify that.

ALTER TABLE [MyTable] ADD  CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ]

Is there a way I can restrict it? I need to raise an error if it is more than 10 character long

Reference: Specify Computed Columns in a Table

Upvotes: 6

Views: 3038

Answers (3)

AnandPhadke
AnandPhadke

Reputation: 13496

Try this:

CREATE TABLE [MyTable]
(
[PGM] VARCHAR(50),
[GRP] VARCHAR(50),
[PGMGRPSEQ] VARCHAR(50)
)

ALTER TABLE [MyTable] ADD  CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ] PERSISTED
GO
ALTER TABLE [MyTable]
ADD CONSTRAINT [MaxLenLimit] CHECK (LEN([CustomNumber]) <= 100)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453152

One way

CREATE TABLE [MyTable]
(
[PGM] VARCHAR(50),
[GRP] VARCHAR(50),
[PGMGRPSEQ] VARCHAR(50),
CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ] PERSISTED CHECK (LEN(CustomNumber) <= 10)
)

Or if you don't want to persist the computed column just add a check constraint that repeats the expression.

CREATE TABLE [MyTable]
(
[PGM] VARCHAR(50),
[GRP] VARCHAR(50),
[PGMGRPSEQ] VARCHAR(50),
CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ],
CHECK (LEN([PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ] ) <= 10)
)

Upvotes: 3

asantaballa
asantaballa

Reputation: 4048

Assuming, say, max length is 100, then:

ALTER TABLE [MyTable] ADD  CustomNumber 
    AS Ltrim(Substring([PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ]  + Space(100), 1, 100))

Upvotes: 1

Related Questions