In SQL Server how to get part of number after decimal point. For example have number:
How to create query to get:
Hi JamesK. Thanks for reply.
It gives result 0.589. But I expected 589
How to get result if need to truncate part of number before decimal point for whole column? The data type of the column float.
What data type do you want it returned as? Int? Varchar?
Hi yosiasz. Thanks for reply.
Can you show me for both:
- for Int
- for Varchar
When working with a language, the first thing to learn about are it's functions.
Hi JeffModen. Thanks for reply.
In the cases when a value has number after decimal point that PARSENAME function works fine. But in case when a value is integer this function just repeat the value in the result. Actually in this case result should be zero. How to solve this problem?
What is the original datatype we're doing this with? I ask because it makes a difference in how to do this.
For example, run this code and imagine some of the difficulties, especially since the number you would want to be returned in this example is actually 590 and not 59.
DECLARE @TestValue DECIMAL(18,9) = 456.590 SELECT PARSENAME(@TestValue,1) ,PARSENAME(@TestValue,2) ,CONVERT(VARCHAR(18),@TestValue)
This should work for a string value - which I assume must be the case since you cannot have 1.23 and 456.590 as numeric values in the same column (1.23 would be 1.230).
Declare @testTable Table (MyNumber varchar(18)); Insert Into @testTable (MyNumber) Values ('1.23'), ('52.89'), ('456.589'), ('1234.1234'), ('123'); Select * , FirstValue = substring(v.MyNumber, 1, p1.pos - 2) , SecondValue = substring(v.MyNumber, p1.pos, p2.pos - p1.pos - 1) From @testTable As tt Cross Apply (Values (concat(tt.MyNumber, '..'))) As v(MyNumber) Cross Apply (Values (charindex('.', v.MyNumber, 1) + 1)) As p1(pos) Cross Apply (Values (charindex('.', v.MyNumber, p1.pos) + 1)) As p2(pos)
The trick here is to make sure you always have at least 2 periods in the string - this way we can always parse out the second element without having to figure out the length of the string or using some default length for the substring.
there is this guy @JeffModen, he is an interesting fella ;), he came up with this function you should look into
Declare @testTable Table (MyNumber varchar(18)); Insert Into @testTable (MyNumber) Values ('1.23'), ('52.89'), ('456.589'), ('1234.1234'), ('123'); ;with src as ( select * from @testTable src cross apply DelimitedSplit8K(src.MyNumber, '.') d ) select * from src where ItemNumber = 2
select cast(replace(cast(1.23%1.0 as varchar),'0.','') as int)
select cast(replace(cast(52.89%1.0 as varchar),'0.','') as int)
select cast(replace(cast(456.589%1.0 as varchar),'0.','') as int)
Select 1.66 - convert( int, 1.66)