How to insert leading zero in SQL Server

Question

This is the code for selecting with leading zeros:

SELECT RIGHT('00000'+ CONVERT(VARCHAR,Asset_No),6) 
  FROM schemaAsset.SystemDefined

How can I insert a value to the column Asset_no with leading zeros?

Example is I am only inserting "1" to the column Asset_no, but in the code, it will insert "000001". And also when I insert two digits like 26, it will insert as "000026". The length does not change. Is this possible in SQL Server?

Answer 1

One way to do this is with an INSTEAD OF trigger.

USE tempdb;
GO

CREATE TABLE dbo.whatever
(
  Asset_No VARCHAR(6)
);
GO

CREATE TRIGGER dbo.fix_whatever
ON dbo.whatever
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.whatever(Asset_No /*, other columns */)
    SELECT RIGHT('000000' + CONVERT(VARCHAR(6), Asset_No), 6)
      /*, other columns */
    FROM inserted;
END
GO

INSERT dbo.whatever(Asset_No) SELECT '22';
GO

SELECT Asset_No FROM dbo.whatever;
GO

Results:

Asset_No
--------
000022

Answer 2

If you want to do the padding in SQL Server, you can do something like this with your INSERT statement:

DECLARE @Asset_no INT = 12;

INSERT INTO schemaAsset.SystemDefined (Asset_no)
  SELECT RIGHT('00000'+ CONVERT(VARCHAR(6),@Asset_No),6)