phan - 1 year ago 60

SQL Question

I have a column X which is full of floats with decimals places ranging from 0 (no decimals) to 6 (maximum). I can count on the fact that there are no floats with greater than 6 decimal places. Given that, how do I make a new column such that it tells me how many digits come after the decimal?

I have seen some threads suggesting that I use CAST to convert the float to a string, then parse the string to count the length of the string that comes after the decimal. Is this the best way to go?

Answer Source

You can use something like this:

```
declare @v sql_variant
set @v=0.1242311
select SQL_VARIANT_PROPERTY(@v, 'Scale') as Scale
```

This will return `7`

.

I tried to make the above query work with a `float`

column but couldn't get it working as expected. It only works with a `sql_variant`

column as you can see here: http://sqlfiddle.com/#!6/5c62c/2

So, I proceeded to find another way and building upon this answer, I got this:

```
SELECT value,
LEN(
CAST(
CAST(
REVERSE(
CONVERT(VARCHAR(50), value, 128)
) AS float
) AS bigint
)
) as Decimals
FROM Numbers
```

Here's a SQL Fiddle to test this out: http://sqlfiddle.com/#!6/23d4f/29

To account for that little quirk, here's a modified version that will handle the case when the float value has no decimal part:

```
SELECT value,
Decimals = CASE Charindex('.', value)
WHEN 0 THEN 0
ELSE
Len (
Cast(
Cast(
Reverse(CONVERT(VARCHAR(50), value, 128)) AS FLOAT
) AS BIGINT
)
)
END
FROM numbers
```

Here's the accompanying SQL Fiddle: http://sqlfiddle.com/#!6/10d54/11