CODEWITHSUNDEEP
sqlsql-serverstringfunction
Macukadam
Macukadam

Reputation: 45

Can't convert decimal numbers with Str function in SQL Server

I want to build a new column that combines two other columns, one is numeric and the other one is nvarchar. I have tried the code below but it got rid of the numbers after comma. 

select ltrim(str([Number])) + ' ' + Units as [Unique] from [Table1]

I want an output like this "0.00 m2". How can I do that?

Upvotes: 0

Views: 147

Answers (3)

Andrey Korneyev
Andrey Korneyev

Reputation: 26866

If you need two digits after comma when using str function - you have to specify it explicitly.

This function syntax is:

STR (float_expression [ , length [ ,decimal ] ] )

Here length is the total length of output string and decimal is the number of places to the right of the decimal point.

So in your case it should be something like

ltrim(str([Number], 10, 2))

Upvotes: 1

marc_s
marc_s

Reputation: 754598

You need to use CAST in T-SQL:

select 
    ltrim(CAST([Number] AS VARCHAR(20))) + ' ' + Units AS [Unique] 
from 
    [Table1]

Upvotes: 1

Shukri Gashi
Shukri Gashi

Reputation: 535

There is no str function in ms sql. You have to use convert function

select ltrim(convert(nvarchar,Number)) + ' ' + Units as [Unique] from Table1

Upvotes: 0

Related Questions