# Create query to get number after decimal point

Hi All
In SQL Server how to get part of number after decimal point. For example have number:
1.23
52.89
456.589
How to create query to get:
23
89
589?
Thanks.

456.589%1.0

1 Like

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.
Thanks

What data type do you want it returned as? Int? Varchar?

Can you show me for both:

1. for Int
2. for Varchar
Thanks.

When working with a language, the first thing to learn about are it's functions.
https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver15

`SELECT PARSENAME(456.589,1);`

1 Like

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?
Thanks.

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
``````

Hello,
select 1.23
select cast(replace(cast(1.23%1.0 as varchar),'0.','') as int)
select 52.89
select cast(replace(cast(52.89%1.0 as varchar),'0.','') as int)
select 456.589
select cast(replace(cast(456.589%1.0 as varchar),'0.','') as int)

1.23

23

52.89

89

456.589

## decimals

589

Select 1.66 - convert( int, 1.66)