SQL: Query integer column in specific format

Question

I am trying to query a column of type (int) into a string of format (000). For example: If the column value was 1 then the output of the query should be 001

How are you doing it? Which DBMS? Post your attempt please. – shree.pat18 Jan 06 '15 at 10:25 — shree.pat18, Jan 06 '15 at 10:25
I apply it on SQL 2000 and SQL 2012 – Ala Jan 06 '15 at 10:36 — Ala, Jan 06 '15 at 10:36

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

Depends on which database engine you use.

In SQL Server 2012 and higher:

SELECT FORMAT(7, 'D3')

The result is this string:

For older versions of SQL Server, see the accepted answer by shree.pat18

For MySQL, look here: Adding a leading zero to some values in column

For PostgreSQL, look here: Padding zeros to the left in postgreSQL

edited May 23 '17 at 11:43

Community

1
1

answered Jan 06 '15 at 10:33

Ruud Helderman

10,563
1
26
45

You are right. I edited my answer accordingly. Thanks for your comment. – Ruud Helderman Jan 06 '15 at 13:22

score 0 · Accepted Answer · answered Jan 06 '15 at 10:37

Try this:

select right('000' + convert(varchar(3), intcolumn), 3) from yourtable

Note that the output is of type varchar. If you will need this output as a number somewhere else, I would suggest doing the formatting in your UI code and keeping it as a number in the query.

SQL: Query integer column in specific format

2 Answers2