More for fun, here is an alternative "solution", set to return six digits and the decimal separator.

😎

USE tempdb;

GO

SET NOCOUNT ON

/* Generate a set of numbers from 1 to

the value of @SAMPLE_SIZE

*/

DECLARE @SAMPLE_SIZE INT = 10;

DECLARE @TEST_SET TABLE (RID INT NOT NULL, FL_VAL FLOAT NOT NULL);

;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),

(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER()

OVER (ORDER BY (SELECT NULL)) AS

N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)

/* Generate floating point number test set */

INSERT INTO @TEST_SET(RID,FL_VAL)

SELECT

NM.N

,ABS(CHECKSUM(NEWID())) / POWER(10.0,ABS(CHECKSUM(NEWID())) % 11.0)

FROM NUMS NM

SELECT

TS.RID AS RID

,TS.FL_VAL AS FLOAT_VALUE

/* If there is a remainder after the division then

the number is too great

*/

,1 -SIGN(FLOOR(TS.FL_VAL / 100000)) AS IS_VALID

/* Return as string

*/

,STR(TS.FL_VAL,7,7) AS CHOPPED_STRING_7

FROM @TEST_SET TS

/* Uncomment the next line to filter only valid numbers */

--WHERE (1 -SIGN(FLOOR(TS.FL_VAL / 100000))) = 1

Sample results

RID FLOAT_VALUE IS_VALID CHOPPED_STRING_7

----------- ---------------------- ---------------------- ----------------

1 769388.178 0 769388

2 2.71267499 1 2.71267

3 176.762866 1 176.763

4 1949056 0 1949056

5 1300132.345 0 1300132

6 1.65171232 1 1.65171

7 0.913729839 1 0.91373

8 1338.032751 1 1338.03

9 70.206716 1 70.2067

10 1974091608 0 *******