Advertisements
Home > SQL > How to implement leading zero to the variable in the SQL Server/Azure

How to implement leading zero to the variable in the SQL Server/Azure


Leading zero to the variable in SQL Server

To implement leading zero to the variable in SQL Server 2008, we can use following script. In some scenario we have to leading zero to the selected values from SQL server table as per the number of digits in the selected value.

 Create a table in SQL Server  

CREATE TABLE Employee(Code INT);

We can insert values in to the age column of the Employee table
    INSERT Employee VALUES(’13’);
    INSERT Employee VALUES(‘111′);
    INSERT Employee VALUES(’12’);
    INSERT Employee VALUES(‘322’);
    INSERT Employee VALUES(‘422’);

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

 Select Code from Employee table

 SELECT * FROM Employee;

 Code
13
111
12
322
422

5 row(s) affected.

 How to Format column value foor leading zero in SQL Server

Now we are going to implement leading zero to each code up to the 4 digits.

 SELECT RIGHT('0000'+ CONVERT(VARCHAR,Code),4) AS EmployeeCode FROM Employee;

 EmployeeCode
0013
0111
0012
0322
0422

 If we want to format with 6 digits can use following scripts

SELECT RIGHT('000000'+ CONVERT(VARCHAR,Code),6) AS EmployeeCode FROM Employee;
Advertisements
  1. April 12, 2013 at 10:25 pm

    I think this web site contains some very great info for everyone. “A sense of share is not a bad moral compass.” by Colin.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: