How to add '00' before "customer id" column (INT datatype) where customer id of-CodePudding

Example 1: for customer id = 1420730 (7 digits), we should show 01420730

Example 2: for customer id = 999999 (6 digits), we should show 00999999

Note: Customer id is of INT datatype, it should remain an INT datatype only after doing the modification

CodePudding user response：

You can use format():

select format(customerid, '0000000')

Note that the result is a string, not a number. Numbers don't have leading zeros.

CodePudding user response：

More complicated but I shy away from FORMAT() because the CLR overhead can be significant:

SELECT RIGHT('0000000'   CONVERT(varchar(11), [Customer id]), 8)
  FROM dbo.tablename;

But it begs the question ... what are you going to do when you're on the front page of hacker news and you get 99 million more users? Or you have to change to bigint?

Example: db<>fiddle

This can be persisted (but more often than not, people want a persisted column for the wrong reasons):

CREATE TABLE dbo.tablename
(
  [Customer id] int,
  PaddedCustomerID AS 
      (CONVERT(char(8), 
       RIGHT('0000000'   CONVERT(varchar(11), [Customer id]), 8))) 
       PERSISTED
);

Example: db<>fiddle

In both cases, though, there is no possible way for SQL Server to consider this padded number an integer. The only way you can keep those 0s there is to store it as a string. This presentation formatting is something that is much better handled at the presentation layer where the values are presented - there is little to gain by storing them this way (persisted or not).

If persisting this column is a requirement, it should be mentioned in your question. It's not fair to change requirements especially when users have already solved the problem as stated. Please read about chameleon questions.