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?
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
精彩评论