CODEWITHSUNDEEP
sqlsql-servert-sql
Azeem
Azeem

Reputation: 2924

How to determine whether the number is float or integer

I need to write this query in SQL Server:

IF isFloat(@value) = 1
BEGIN
    PRINT 'this is float number'
END
ELSE
BEGIN
    PRINT 'this is integer number'
END

Please help me out with this, thanks.

Upvotes: 13

Views: 59295

Answers (4)

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

See whether the below code will help. In the below values only 9, 2147483647, 1234567 are eligible as Integer. We can create this as function and can use this.

CREATE TABLE MY_TABLE(MY_FIELD VARCHAR(50))
INSERT INTO MY_TABLE
VALUES('9.123'),('1234567'),('9'),('2147483647'),('2147483647.01'),('2147483648'), ('2147483648ABCD'),('214,7483,648')

SELECT *
FROM MY_TABLE
WHERE CHARINDEX('.',MY_FIELD) = 0 AND CHARINDEX(',',MY_FIELD) = 0       
AND ISNUMERIC(MY_FIELD) = 1 AND CONVERT(FLOAT,MY_FIELD) / 2147483647 <= 1
DROP TABLE MY_TABLE

OR

DECLARE @num VARCHAR(100)
SET @num = '2147483648AS'
IF ISNUMERIC(@num) = 1 AND @num NOT LIKE '%.%' AND @num NOT LIKE '%,%' 
BEGIN
    IF CONVERT(FLOAT,@num) / 2147483647 <= 1
        PRINT 'INTEGER'
    ELSE
        PRINT 'NOT-INTEGER' 
END
ELSE
    PRINT 'NOT-INTEGER'

Upvotes: 1

Saradhi
Saradhi

Reputation: 11

DECLARE @value FLOAT = 1.50
IF CONVERT(int, @value) - @value <> 0
BEGIN
    PRINT 'this is float number'
END
ELSE
BEGIN
    PRINT 'this is integer number'
END

Upvotes: 1

Nico van Niekerk
Nico van Niekerk

Reputation: 37

Martin, under certain circumstances your solution gives an incorrect result if you encounter a value of 1234.0, for example. Your code determines that 1234.0 is an integer, which is incorrect.

This is a more accurate snippet:

if cast(cast(123456.0 as integer) as varchar(255)) <> cast(123456.0 as varchar(255)) 
begin 
  print 'non integer' 
end 
else 
begin 
  print 'integer' 
end

Regards,

Nico

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453328

declare @value float = 1

IF FLOOR(@value) <> CEILING(@value)
BEGIN
    PRINT 'this is float number'
END
ELSE
BEGIN
    PRINT 'this is integer number'
END

Upvotes: 47

Related Questions