C. Suttle C. Suttle - 1 month ago 6
SQL Question

Why Does SQL Len Function Return 7 for this Query?

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?

fvu fvu
Answer

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