How to Convert Int to String with Leading Zero in SQL Server?

by

In this SQL Server tutorial, you will learn how to convert int to string with leading zero in SQL Server.

Where you will understand how to convert the given integer value into a string with a number of zeros at the beginning of that string.

After that, I will explain how to use the functions CONVERT(), RIGHT(), CAST(), and REPLICATE() in combination to convert int to string with leading zero.

SQL Server Convert int to String with Leading Zero

First, you must understand “What does leading zero mean?” when you put a zero at the beginning of a sequence of digits in a number, then you say a number with a leading zero.

Leading zeros that appear at the beginning of the number make sure that the number maintains a specific length or format. Thus, the leading zeros help format the number.

For example, 001 contains two leading zeros before the digit 1.

So, in SQL Server, you will use different methods or functions to convert int to string with leading zero.

Convert int to String with Leading Zero: using Convert() and Right() Function

The CONVERT() function converts one data type to another, and the RIGHT() function for leading zeros.

The syntax of the convert() function is given below.

CONVERT(data_type, exp)

Where,

  • CONVERT(): It is the function that converts the given data type to another data type.
  • data_type: Name of the data type to which you want to convert.
  • exp: The expression or value that you want to convert.

Now, next, let’s know about the FORMAT() function syntax.

RIGHT(exp, length)

Where,

  • RIGHT(): The function extracts characters from the right side of a string.
  • exp: It is the expression or value from which you will extract the character
  • length: The number of characters you want to extract from the right side of the given string.

Remember you use the CONVERT() function with a combination of RIGHT() functions. First, use the CONVERT() function to convert int to string, and then the result from the CONVERT() function is passed to the RIGHT() function to add the leading zeros.

Convert int to String with Leading Zero using Convert() and Right() Function Table Orders

For example, suppose you have access to the Orders table of the store database of the e-commerce-based company in the USA, and the Orders table contains columns order_id and order_number, as shown below.

Look at the Orders table column order_number that contains the integer value. To convert this integer value to a string with leading zeros. For that, write the below query and execute it.

SELECT 
    order_id, 
    RIGHT('00000' + CONVERT(VARCHAR,order_number), 5)
FROM Orders;
Convert int to String with Leading Zero using Convert() and Right() Function

The above query converts the order_number column value from int to string and then adds the leading zeros to that converted value.

Let’s break down this query part RIGHT(‘00000’ + CONVERT(VARCHAR,order_number), 5).

  • First, the query CONVERT(VARCHAR,order_number) converts the order_number column value to VARCHAR (string) data type.
  • Then, the function RIGHT(‘00000’ + …) takes that string value and adds the five zeros at the beginning of each string value in a column.
  • After that, RIGHT(…+ 5) extracts the rightmost 5 characters from the string, trimming the leading zeros If there are more than 5 characters in the string value.

Now you can see in the output that all the values of the order_number column are converted to string values with leading zeros, for example, 00123, 04567, etc.

Convert int to String with Leading Zero: using Cast() and Replicate() Function

In the previous section, you learned how to combine the convert() and right() functions to convert int to string with leading zero.

Here, you will learn about other CAST() and REPLICATE() functions for converting int to string with leading zeros.

The CAST() function also converts the given value from one data type to another.

The syntax is given below.

CAST(exp AS data_type (length))

Where,

  • CAST(): The function casts one data type to another.
  • exp: It is the expression or value you want to cast or convert into another data type.
  • As data_type: Name the data type to which you want to convert your given expression or value.
  • length: It is used to specify the length of the data type.

Now you know about the syntax of the CAST() function, let’s know the syntax of the REPLICATE() function, which repeats a given string a specified number of times.

The syntax is given below.

REPLICATE (string_exp, int_exp)

Where,

  • REPLICATE: The function repeats a given string a specified number of times.
  • string_exp: It is the actual string that you want to repeat.
  • int_exp: The integer value means how often you want to repeat the given string.

The replicate function accepts the string (string_exp) and duplicates the number of times specified by integer value (int_exp).

Again, convert the order_number column from int to string with a leading zero using the CAST() and REPLICATE() functions.

Run the below query.

SELECT 
    order_id,
    CAST(REPLICATE('0', 5 - LEN(CAST(order_number AS VARCHAR(5)))) + 
	CAST(order_number AS VARCHAR(5)) AS VARCHAR(5)) AS order_number
FROM Orders;
Convert int to String with Leading Zero using Cast() and Replicate() Function

When you execute the above query, it converts the order_number column value from int into a string.

Let’s understand the query part one by one.

  • The query part LEN(CAST(order_number AS VARCHAR(5))), this part used the CAST() to convert the order_number column value from integer to VARCHAR (string) data type.
  • Then, the LEN() function computes the length of the value, which is a string in the order_number column. Here, the purpose is to know how many leading zeros need to be added to achieve five characters.
  • After that, the query part REPLICATE(‘0’, 5- LEN(…)); this part of the query creates a string of zero that will be added at the beginning of the string.
  • It computes the difference between 5 (the required length) and the string length within the ‘order_number’ column. So, this calculated difference decides how many leading zeros should be added to the string.
  • Finally, the query part ‘…’ + CAST(order_number AS VARCHAR(5)) adds the leading zeros with the string in order_column and creates a new string with leading zeros.

As you can see in the output, the order_number column value is converted to a string with a leading zero. For example, 89 was the integer value in the order_number column, but after conversion, it becomes 00089, which contains three zeros at the beginning of the value.

Conclusion

In this SQL Server tutorial, you learned how to convert int to string with leading zeros in SQL Server, where you learned about the meaning of leading zero. Finally, you used some SQL Server function and converted the given int to a string with a leading zero.

You may like to read the following tutorials: