Why Does SQL Len Function Return 7 for this Query?

Question

For this TSQL query:

SELECT CAST(116.33 / 8 AS FLOAT) AS [Float Value]
 , LEN(CAST(116.33 / 8 AS FLOAT)) AS [Length of Float Value]
 , LEN(CAST(CAST(116.33 / 8 AS FLOAT) AS VARCHAR)) AS [Length Cast As Varchar]

Result:

Float Value  | Length of Float Value  |  Length Cast As Varchar
14.54125     | 7                      |  7

Why does LEN yield 7 instead of 8 when I cast as Varchar? Also, what implicit cast or conversion will yield 8 for LEN?

Answer 1

Len function on Float in SQLServer gives wrong length is very related and @rbr94 's answer there explains it quite well:

LEN() works on [N]VARCHAR(), thus you're running into an implicit conversion from FLOAT to VARCHAR

It's easily proven by extending your query:

SELECT CAST(116.33 / 8 AS FLOAT) AS [Float Value]
 , LEN(CAST(116.33 / 8 AS FLOAT)) AS [Length of Float Value]
 , LEN(CAST(CAST(116.33 / 8 AS FLOAT) AS VARCHAR)) AS [Length Cast As Varchar]
 , CAST(CAST(116.33 / 8 AS FLOAT) AS VARCHAR) AS [Cast As Varchar]

which gives

Float Value Length of Float Value   Length Cast As Varchar  Cast As Varchar
14.54125    7                       7                       14.5413