How to automatically pad a SQL Server column

Question

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)

Did you try this SQL - insert into table (ticketno,...) values (RIGHT('00000'+@TicketNo, 5),...); ? — rs.
– rs., Commented Mar 26, 2013 at 18:03
I have no control as to the actual INSERTs, they are done outside our group. At present I sometime run an UPDATE to set them correctly. What I was thinking it some kind of constraint to enforce the leading zeros automatically, if that is at all possible. — dawntrader
– dawntrader, Commented Mar 26, 2013 at 18:10

Community · Accepted Answer · 2017-05-23 12:17:27Z

2

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) 
)

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Mar 26, 2013 at 18:09

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Jason Whitish Over a year ago

char(5) seems like a good idea, but the check constraint should probably take length into account as well, so something like LEN(T) = 5 AND T NOT LIKE '%[^0-9]%'

Mikael Eriksson Over a year ago

@JasonWhisman Not at all necessary with a char(5). It will always be 5.

GilM Over a year ago

This will cause inserts to fail if TicketNo is not 5 digits. That's one interpretation of "enforce". If the OP wants automatically update the value upon insert or update, rather than fail the operation, he might need an Insert,Update Trigger.

Mikael Eriksson Over a year ago

@GilM Only OP knows :). If that was the case I would personally turn the column into an integer with check < 100000 and a computed column that pads the zeros.

GilM Over a year ago

Also, and this is just a pet peeve of mine.. LEN(TicketNo) will not always be 5, even if TicketNo is a char(5) because LEN, for some idiotic reason, does not count trailing spaces. DATALENGTH(TicketNo) would always be 5...But, I'm not bitter. :-)

|

Abe Miessler · Accepted Answer · 2013-03-26 18:08:37Z

1

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.

answered Mar 26, 2013 at 18:08

Abe Miessler

85.7k104 gold badges324 silver badges496 bronze badges

Collectives™ on Stack Overflow

How to automatically pad a SQL Server column

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related