CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Daniel T.
Daniel T.

Reputation: 38400

Pad a number with zeros, but only if it's below a certain length?

I have numbers that must be at least 7 digits long. For example:

0000001
123456789
0012345

Are all valid. I only need to pad the number with 0's only if its length is below 7. How do I do this in SQL Server? The best I've been able to get is to pad the number if the length is less than 7, but above that, it starts to truncate the number instead.

Upvotes: 1

Views: 6212

Answers (3)

AnandPhadke
AnandPhadke

Reputation: 13506

Hi check this out.

declare @num table(num varchar(10))
insert into @num
VALUES('0000001'),('123456789'),('0012345'),('123'),('11')

select CASE when len(num) < 7 then REPLICATE('0',(7-len(num)))+num else num END from @num

Upvotes: 0

Christopher Broome
Christopher Broome

Reputation: 375

Aaron Bertrand beat me to it.

I'd like to add that it might also be useful to encapsulate both the character and number of times you have to repeat it, so that if it needs to change sometime in the future, it's easy to do it. You can do this using REPLICATE. So expanding on Aaron's example:

DECLARE @num_digits as int = 7
SELECT RIGHT(REPLICATE('0', @num_digits) + col, @num_digits) FROM dbo.table;

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

 SELECT CASE WHEN LEN(CONVERT(VARCHAR(12), column)) > 7 THEN 
    CONVERT(VARCHAR(12),column) ELSE
     RIGHT('0000000' + CONVERT(VARCHAR(12), column), 7) END
  FROM dbo.table;

Upvotes: 7

Related Questions