How to use STR in Sql Server

Intro

SQL Server provides the STR function to convert numerics to character values. In this article, we will learn how to use the STR function in SQL Server.

The Syntax

The basic syntax of a STR is as follows:

SELECT STR(num, length, decimal);

• num is a single or list numbers to convert to character values.
• length is the size of the string which will include sign, digits, spaces
• decimal is how many decimals to round to

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

A Simple Example

We begin with a basic float number to turn into a string. Here we use the number 10.456. We set the length to 5 and the decimal to 2 to round off the flow to 2 decimal places.

select str(10.456, 5, 2) as res;

One other important example is if we specify a length that is smaller than the size of the response, SQL will return a list of asterisks (*).

select str(10.456, 1, 2) as res;