How to Convert Int to String with Commas in SQL Server?

by

In this SQL Server tutorial, you will learn about SQL Server convert int to string with commas.

I will show you how to use the CONVERT(), FORMAT(), and CAST() functions to convert integers to strings and format those strings with commas in SQL Server.

SQL Server Convert Int to String with Commas

There are two ways to convert the given integer to a string with commas in SQL Server. The first uses the CONVERT() and FORMAT () functions, and the second uses the CAST() and FORMAT() functions.

SQL Server Convert Int to String with Commas Using CONVERT() and FORMAT() functions

The CONVERT() function takes the value or the column value and converts it into another data type. Suppose you pass an integer value to the CONVERT() function, which can then convert that value into strings, decimals, etc.

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.

The FORMAT() function formats the given value based on the specified format. Let’s quickly jump to an example.

The syntax is given below.

FORMAT(exp_or_value, format)

Where,

  • FORMAT(): The function formats the given value into the specified format.
  • exp_or_value: The expression or value that you are going to format. It can be a number, date, time, etc.
  • format: The format in a string determines how you want to format the given value.

Now let’s understand with an example.

Suppose you have access to the sales table with columns SalesID and TotalSales and are given the task of converting the value within the TotalSales column into a string value. After conversion, you have to format the string with commas.

The sales table is shown below.

SQL Server Convert Int to String with Commas Using Convert() and FORMAT() Function Table Sales

Now execute the below query that converts the int into a string with commas.

SELECT CONVERT(VARCHAR, FORMAT(TotalSales, '##,##')) as Formatted_string 
FROM sales;
SQL Server Convert Int to String with Commas Using CONVERT() and FORMAT() Function

When you execute the above query, first, the query format the value within the TotalSales column with commas using the FORMAT() function. Then, convert the data type of the TotalSales column to VARCHAR (string).

Within the FORMAT() function, format ‘##,##’ means put the commas after two digits. So, in the result set, you can see the value ’12, 500′ contains a comma after the 2nd digit, and the same is true for all other values.

This is how to convert int to string with commas in SQL Server using CONVERT() and FORMAT() functions.

SQL Server Convert Int to String with Commas Using CAST() and FORMAT() functions.

The CAST() is the same as the CONVERT() function and converts the given value into another data type. As you know from the previous section, the FORMAT() function formats the value based on the specified format.

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.

After understanding how the CAST() function works, for example, here, use the same table Sales that you have used above. Now execute the below query.

SELECT CAST(FORMAT(TotalSales, '##,##') as VARCHAR) as Formatted_string 
FROM sales;
SQL Server Convert Int to String with Commas Using CAST() and FORMAT() Function

When you execute the above query, the query formats the value within the TotalSales column with commas using the FORMAT() function. Then, convert the data type of the TotalSales column to VARCHAR (string) using the CAST() function.

So again, in the output, you can see that the integer value is converted into a string with commas. For example, the value 15,000 contains commas after the 15 digits.

This is how to combine the given integer to a string with commas in SQL Server using the CAST() and FORMAT() functions.

Conclusion

In this SQL Server tutorial, you learned how to convert integer to string with commas in SQL Server, where you learned about two methods, the first method CONVERT() and FORMAT(), and the second is the CAST() and FORMAT().

You may also like: