Hi nagaraju60,
Refer the below sample query.
-- For Varchar DataType
DECLARE @Test AS TABLE(Number VARCHAR(50))
INSERT INTO @Test VALUES('3.14')
INSERT INTO @Test VALUES('5.999')
INSERT INTO @Test VALUES('9.555')
INSERT INTO @Test VALUES('8.55')
SELECT Number
FROM @Test
WHERE LEN(PARSENAME(Number,1)) < 3
-- For Decimal DataType
DECLARE @Test AS TABLE(Number DECIMAL(18,17))
INSERT INTO @Test VALUES(3.14)
INSERT INTO @Test VALUES(5.999)
INSERT INTO @Test VALUES(9.555)
INSERT INTO @Test VALUES(8.55)
SELECT CAST(REPLACE(RTRIM(REPLACE(CAST(CAST(Number AS DECIMAL(18,17)) AS NVARCHAR(MAX)),'0',' ')),' ','0') AS FLOAT) Number
FROM @Test
WHERE LEN(PARSENAME(CAST(REPLACE(RTRIM(REPLACE(CAST(CAST(Number AS DECIMAL(18,17)) AS NVARCHAR(MAX)),'0',' ')),' ','0') AS FLOAT),1)) < 3
Input
| Number |
|---|
| 3.14 |
| 5.999 |
| 9.555 |
| 8.55 |
