# How to use Ltrim, nstr for a decimal value

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

Try this:

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

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
`````` ``````SELECT   'Using My Formula'
,  VARIABLE
,  cast( CAST(VARIABLE*10 AS int)/10.0 AS decimal(10,1) )
FROM #data
GO
``````  All depends on

Rounded Up
OR
Without Rounded Up

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

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

Thanks,
Jeff

This is even better.. than my solution

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

Thankyou Jeff

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.

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