In a field I get values like
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
Any help much appreciated
SELECT FORMAT(columnName, 'N1')
I tried to do this ...
i have my own calculation !!!
please click arrow to the left for DROP Create Sample Data
drop table #data
create table #data
INSERT INTO #data SELECT 35.0945
INSERT INTO #data SELECT 15.547
INSERT INTO #data SELECT 152.473
SELECT 'Sample Data', * from #data
SELECT 'Using My Formula'
, cast( CAST(VARIABLE*10 AS int)/10.0 AS decimal(10,1) )
All depends on
Whether you want the answer
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))
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))
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)
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.