Automatically CONCATENATE text on data entry

Question

I am a newbie and need help.

I have a table called "Employees". It has 2 fields [number] and [encode]. I want to automatically take whatever number is entered into [number] and store it in [encode] so that it is preceded by the appropriate amount of 0's to always make 12 digits.

Example: user enters '123' into [number], '000000000123' is automatically stored in [encode] user enters '123456789' into [number], '000123456789' is automatically stored in [encode]

I think i want to write a trigger to accomplish this. I think that would make it happen at the time of data entry. is that right?

The main idea is would be something like this:

variable1 = LENGTH [number]

variable2 = REPEAT (0,12-variable1)

variable3 = CONCATENATE (variable2, [number])

[encode] = variable3

I just don't know enough to make this happen

ANY help would be FANTASTIC.

I have SQL-SERVER 2005 and both fields are text

Answer 1

The following link provides two methods to pad a number with 0's to make it a specific length.

http://blog.sqlauthority.com/2009/03/10/sql-server-pad-ride-side-of-number-with-0-fixed-width-number-display/

In a trigger it might look like this:

CREATE TRIGGER InsertEncodePadded
ON Employees
INSTEAD OF INSERT 'Use instead of to make sure you don't get start a recursive insert
AS
   BEGIN
      DECLARE @number varchar(12);
      DECLARE @encoded varchar(12);

      SELECT @number = [number] from inserted;
      SET @encoded = RIGHT(REPLICATE('0', 12) + @number, 12);

      INSERT INTO Employees VALUES(@number, @encoded);
   END
GO

I don't have SQL Server currently, so the code might not be exactly correct, but is should steer you in the right direction.

Answer 2

Thank You VERY much!

I will give this a try.