How to use Ltrim, nstr for a decimal value

Transact-SQL
1

Hi All,

In a field I get values like
35.0945,
15.547,
152.473
I need to use a expression in such a way that . I need to get all the numbers before decimal and after decimal just the first digit.

Solution for the above field
35.0,
15.5,
152.4

Any help much appreciated
Thankyou
Pradnya

2

Try this:

SELECT FORMAT(columnName, 'N1')
FROM dbo.MyTable
3

hi

I tried to do this ...

i have my own calculation !!!

please click arrow to the left for DROP Create Sample Data 
drop table #data
go 

create table #data 
(
VARIABLE DECIMAL(10,5)
)
go 

INSERT INTO #data SELECT 35.0945
INSERT INTO #data SELECT 15.547
INSERT INTO #data SELECT 152.473
go 

SELECT 'Sample Data', * from #data

image

SELECT   'Using My Formula' 
        ,  VARIABLE
        ,  cast( CAST(VARIABLE*10 AS int)/10.0 AS decimal(10,1) )  
 FROM #data
 GO

image

4

image

5

All depends on
Whether you want the answer

Rounded Up
OR
Without Rounded Up

You can also do it In third way
Using string function s

6

I would be careful recommending any usage of FORMAT - as that function has been proven to be slower and less efficient than other methods. In fact, it has been shown to be extremely slow compared to other methods.

 Select cast(35.0945 As numeric(5,1))
      , cast(15.547 As numeric(5,1))
      , cast(152.473 As numeric(5,1))

This will round the number - but if we want it truncated:

 Select cast(round(35.0945, 1, 1) As numeric(5,1))
      , cast(round(15.547, 1, 1) As numeric(5,1))
      , cast(round(152.473, 1, 1) As numeric(5,1))

For consistency:

 Select cast(round(35.0945, 1, 0) As numeric(5,1))
      , cast(round(15.547, 1, 0) As numeric(5,1))
      , cast(round(152.473, 1, 0) As numeric(5,1))
2 Likes
7

Thanks,
Jeff

This is even better.. than my solution

8

It may not be safe to convert to a numeric value, and there's no need to do it in this case anyway:

SELECT LEFT(value, CHARINDEX('.', value + '.') + 1) AS truncated_value
FROM ( VALUES('35.0945'),('15.547'),('152.473'), ('1498'),('123.4ABC') ) AS test_values(value)
1 Like
9

Thankyou Jeff

10

Although it may not be safe to convert - the OP specifically mentioned in the subject that the values are decimal. Since the values are decimal (numeric) - converting to a numeric/decimal type will be safe.

11

Only if the underlying data type is actually decimal. If it's char, at some point, bad data will get in, some how, some way.

1 Like