Search code examples
sqlselectzerofill

TSQL/MSSQL Select - zero fill integer

I have an SQL select statement:

select pk from items

it returns:

1
2
4
29
8
987654
12313232

Now my boss wants something like:

000001
000002
000004
000029
000008
987654
12313232

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?

Solution

  • 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