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 in formatting the number.
For example, the number 001 contains two leading zeros before the digit 1.
So, in SQL Server, to convert int to string with leading zero, you will use different methods or functions.
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(): 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(): It is the function that extracts characters from the right side of a string.
- exp: It is the expression or value from which you are going to extract the character
- length: The number of characters that 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 for adding the leading zeros.
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;
When running the above query, it 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, which means trimming the leading zeros If there are more than 5 characters in the string value.
Now you can see in the output all the value of the order_number column is converted to the string value 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 use the convert() and right() functions in combination to convert int to string with leading zero.
Here, you will learn about another two functions, the cast() and replicate() 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))
- CAST(): It is the function that casts one data type to another.
- exp: It is the expression or value that you want to cast or convert into another data type.
- As data_type: Name of 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)
- REPLICATE: It is the function that repeats a given string a specified number of times.
- string_exp: It is the actual string that you want to repeat.
- int_exp: It is the integer value, which means how many times you want to repeat the given string.
The replicate function accepts the string (string_exp) and duplicates it the number of times specified by integer value (int_exp).
Now again, convert the order_number column from int to string with leading zero using the CAST() and REPLICATE() function.
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;
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 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 a total length of 5 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.
- Basically, It computes the difference between 5 (which is the required length) and the length of the string 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 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.
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:
- Convert Int to String in Stored Procedure in SQL Server
- Convert Int to Fixed Length String in SQL Server
- SQL Server Convert Int to String Padding
- How to Format Number with Commas and Decimal in SQL Server?
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.