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
– Luc M, Commented Nov 28, 2013 at 20:41
You should better store numbers instead of strings and format them in your program logic. — juergen d
– juergen d, Commented Nov 28, 2013 at 20:41
I would suggest you read: stackoverflow.com/questions/9520661/…. — Paul92
– Paul92, Commented Nov 28, 2013 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
– Davevmb, Commented Nov 28, 2013 at 20:45

NoChance · Accepted Answer · 2013-11-28 20:57:58Z

4

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

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

answered Nov 28, 2013 at 20:57

5,7844 gold badges38 silver badges50 bronze badges

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

Comments

Indi · Accepted Answer · 2013-11-28 21:01:24Z

3

Try this:

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

answered Nov 28, 2013 at 21:01

33.9k7 gold badges61 silver badges80 bronze badges