SQL - How to Get Only the Numbers After the Decimal

SQL - How do I get only the numbers after the decimal?

one way, works also for negative values

declare @1 decimal(4,3)
select @1 = 2.938

select PARSENAME(@1,1)

How to display two digits after decimal point in SQL Server

select cast(your_float_column as decimal(10,2))
from your_table

decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before.

The biggest possible number would be 99999999.99

Get only decimal part in float number in SQL Server

you can get first four decimal parts by :

select SUBSTRING (PARSENAME(5.4564556,1), 1, 4)

Get the number of digits after the decimal point of a float (with or without decimal part)

I found some simple script (relatively to me) to handle this.

ISNULL(NULLIF(CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))),0) - 1,0)

Here the ISNULL(NULLIF is only to handle the float without decimal part.
If there is no values without decimal part, then it is very simple

CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))) -1 

Hope this will be helpful to you.
Full script below

declare @YourTable table (Amount float)
insert into @YourTable
values(123),(123.1),(123.0123),(123.789456)

SELECT  ISNULL(NULLIF(CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))),0) - 1,0)
FROM    @YourTable

SELECT  CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), Amount, 128))) -1 
FROM    @YourTable

How to extract first number after decimal point in value

try like below

select substr(to_char(15.7,'9999.0'),-1,1) as col from dual
 it will return 7

How can I get only the first decimal of a number but without rounding it

You can use this :

sELECT CAST (ROUND(2.488888 , 1, 1) AS decimal(18,1))

sELECT CAST (ROUND(2.4999999 , 1, 1) AS decimal(18,1))

How to get 4 digit numbers after decimal including before decimal also in SQL

How about using cast with decimal and scale of 4. Something like:

select cast(101.32650000 as decimal(10,4));

• SQL Fiddle Demo

Select numbers with more than 4 decimal places

DECLARE @tbl TABLE (val float)
INSERT INTO @tbl SELECT 1234.567
INSERT INTO @tbl SELECT 1234.5678
INSERT INTO @tbl SELECT -1234.5678
INSERT INTO @tbl SELECT 1234.56789

SELECT *
from @tbl
where (((val*10000) - CONVERT(INT,(val*10000))) <> 0)

how to get only two number after decimal

Try this:

SELECT
    ROUND(YourColumn,2)
    FROM ...

Test it out:

DECLARE @YourTable table (RowValue money)
INSERT @YourTable VALUES (123.4321)
INSERT @YourTable VALUES (0.001)
INSERT @YourTable VALUES (1.1251)

SELECT  
    RowValue, ROUND(RowValue,2) AS TwoPlaces
    FROM @YourTable

OUTPUT:

RowValue              TwoPlaces
--------------------- ---------------------
123.4321              123.43
0.001                 0.00
1.1251                1.13

(3 row(s) affected)

---

Related Topics