SQLTeam.com | Weblogs | Forums

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

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

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

Hi yosiasz. Thanks for reply.
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

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?
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)

Output:
input

1.23

decimals

23

input

52.89

decimals

89

input

456.589

decimals

589

Select 1.66 - convert( int, 1.66)