TSQL/MSSQL Select - zero fill integer

Question

I have an SQL select statement:

select pk from items

it returns:

Now my boss wants something like:

He definitely want the output to be six digits minimum (which I think nonsense).

I've tried doing something like '00000' + convert(nvarchar(6),pk) which works okay only if the pk is just one digit. Do you have any idea how to do this? Should I use conditional statement?

@nik, and if it exceeds? I'll use it in my Windows Forms Report(.rdlc), also I'm using DataSet(.xsd) — kazinix, Aug 01 '11 at 08:36
Use "#000000" so the format string will have at least 6 digits, when less will add zero's — niktrs, Aug 01 '11 at 08:38
Don't add overhead to sql code if something can be done through reporting services — niktrs, Aug 01 '11 at 08:40

gbn · Accepted Answer · 2011-08-01T08:25:51.667

5

~~Normally this~~

RIGHT ('000000' + CONVERT(varchar(6), pk), 6)

But as you have values more than 6 digits, try this

LTRIM(RIGHT ('    000000' + CONVERT(varchar(10), pk), 10))

But you need a CASE to deal with numbers that are 7-9 digits long:.

CASE
    WHEN pk >= 1000000 THEN CONVERT(varchar(10), pk)
    ELSE RIGHT ('000000' + CONVERT(varchar(6), pk), 6)
END

edited Aug 01 '11 at 08:25

answered Aug 01 '11 at 08:01

gbn

422,506
82
585
676

How if it's more than 6 digits? Also, cast doesn't work in my query, I'm using 2008. – kazinix Aug 01 '11 at 08:04
I think I really need a conditional statements. Either your first and second answer doesn't work right. Thanks anyway. – kazinix Aug 01 '11 at 08:21

TSQL/MSSQL Select - zero fill integer

1 Answers1