CODEWITHSUNDEEP
sql-servert-sql
dawntrader
dawntrader

Reputation: 775

How to automatically pad a SQL Server column

My SQL Server table has a column defined as:

TicketNo varchar(5)

The rows in this table are inserted by some bulk load files from different sources.

Now, depending on who prepared the bulk load input files, sometimes TicketNo has leading 0s, sometimes not.

How can I enforce INSERTS to the table so that TicketNo will always be set with leading zeros, something like: 

TicketNo = RIGHT('00000'+TicketNo, 5)

Upvotes: 2

Views: 325

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use a char(5) column with a check constraint.

create table YourTable
(
  TicketNo char(5) check (patindex('%[^0-9]%', TicketNo) = 0)
)

Update:

Using this answer by Martin Smith it could look like this instead to make sure there are only 0-9 allowed.

create table YourTable
(
  TicketNo char(5) collate Latin1_General_CS_AS check (patindex('%[^0123456789]%', TicketNo) = 0) 
)

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85056

How you enforce it is a tricky question. My first through would be to create a stored procedure and force all inserts to take place through that. Then you could use rs's solutions.

Other than that you can create an insert/update trigger that checks for leading zeros.

Upvotes: 1

Related Questions