Sql - making all items 3 digits in length

Question

I don't know if this is possible in SQL Server but I'm gotta ask it ;-)

I have a column called duty in a table work.

So say Work.Duty contains different numbers, like so (1, 2, 3, 20, 22, 305, 306, etc...)

A duty number should always be 3 digits long, but they are imported from a flat file and they are supplied as is.

Is it possible to add 1 or 2 zero's in a statement so all duty numbers have 3 digits (001, 002, 020, 021, 305, 306)?

Yeah, it;s possible. Add 1000 and take the 3 first characters starting from right. — Luc M, Nov 28 '13 at 20:41
You should better store numbers instead of strings and format them in your program logic. — juergen d, Nov 28 '13 at 20:41
I would suggest you read: http://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server. — Paul92, Nov 28 '13 at 20:42
I am using MS SQL 2008. I think i have enough possible solutions to go on. Thx for the help and Paul92 thanks for the link. — Davevmb, Nov 28 '13 at 20:45

score 3 · Answer 1 · answered Nov 28 '13 at 20:57

3

Assuming that yourColumn is Varchar(...), try:

SELECT FORMAT(CAST(yourColumn AS INT),'000','en-US') from yourTable;

answered Nov 28 '13 at 20:57

NoChance

score 3 · Answer 2 · answered Nov 28 '13 at 21:01

3

Try this:

SELECT RIGHT(1000 + Duty, 3) AS newDuty
FROM Work

answered Nov 28 '13 at 21:01

Kaf

2 Answers2