Len Function on Float in SQLserver Gives Wrong Length

Why Does SQL Len Function Return 7 for this Query?

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

TSQL LEN function returns a 1 when evaluating a zero-length integer

An INT doesn't have a length. This is what you are actually doing...

• Create an INT variable
• Create a zero length string ''
• CAST that string to an INT 0
• Assign the result to the variable
• CAST the variable's value to a STRING '0'
• Caclulate the length of that string

The result of which is that your LEN function is not being called on '', but is infact being called on '0', which has a length of 1.

How to reduce the float length

There is a very simple solution. You can find it in BOL. Round takes an optional 3rd argument, which is round type. The values are round or truncate.

ROUND numeric_expression , length [ ,function ] )

...

function Is the type of operation to perform. function must be
tinyint, smallint, or int. When function is omitted or has a value of
0 (default), numeric_expression is rounded. When a value other than 0
is specified, numeric_expression is truncated.

So just do

Select ROUND(cast(23 as float)/12, 2, 1) as total

That gives 1.91. Note, if you were really seeing 1.999 - something is really wrong with your computer. 23/12 = 1.916666666(ad infinitum). You need to cast one of the numbers as float since sql is assuming they're integers and doing integer division otherwise. You can of course cast them both as float, but as long as one is float the other will be converted too.

TSQL LEN(Substring) returning wrong number

LEN Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

DATALENGTH Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. The DATALENGTH of NULL is NULL.

EDIT

I think I got it. Many times you mention about ".". In my opinion you have float value which is implicitly converted to varchar. Check below.

SELECT LEN(t.val), t.val
FROM  (SELECT 20.0) AS t(val)

Len function : unicode character counted as 2

For some characters, there are several ways they can be encoded as Unicode.

In this case, an "ó" can be either U+00F3 (one 16-bit value) or U+006F U+0301 (two 16-bit values). These forms are canonically equivalent.

If you feel like reading a bit more, Using Unicode Normalization to Represent Strings by Microsoft.

Unfortunately, there is no way in T-SQL to convert a string from one form into another. See also questions like Normalize unicode string in SQL Server?

But the good news is since they're canonically equivalent, they compare the same in T-SQL (you can write N'Không có'=N'Không có' and the result is true) so it's not that big a problem as you may think at first.

How to get the count of digits after the decimal point in a float column in ms sql?

I have received from my friend a very simple solution which is just great. So I will post the workaround in order to help others in the same position as me.

First, make function:

create FUNCTION dbo.countDigits(@A float) RETURNS tinyint AS
BEGIN
declare @R tinyint
IF @A IS NULL 
   RETURN NULL
set @R = 0
while @A - str(@A, 18 + @R, @r) <> 0
begin
   SET @R = @R + 1
end
RETURN @R
END
GO

Second:

select MY_FIELD,
dbo.countDigits(MY_FIELD) 
from MY_TABLE

Using the function will get you the exact number of digits after the decimal point.

---

Related Topics