I was reading someone’s question the other day on StackExchange and while the existing comments/questions solved the OP’s (original poster) problem they didn’t really answer the question. I was pretty sure I understood what was going on so I decided to throw my own answer into the ring. In the process of showing the OP what was going on I realized I was right, but that the results were interesting (well to me anyway) as well. That being the case I decided to share (that’s what a blog is for right?).

TL;DR; LEN doesn’t work on non-strings. It does a CONVERT_IMPLICIT behind the scenes which can give you some odd results.

The OP was using the LEN function to determine the size of the data he had in a column. Well, actually he wanted to know the number of characters he saw visually displayed by the computer. Unfortunately the column he was using was of datatype FLOAT. Why does that matter? Well from what I can tell LEN only works on strings. Behind the scenes it will use a CONVERT_IMPLICIT operator to change the value into a varchar. This isn’t really a big deal if he was using an INT datatype that actually converts cleanly into varchar (i.e. it looks the same). FLOAT however can get converted into scientific notation when it goes to a string.

Proof! I want Proof!

If you run the following code:

CREATE TABLE #temp (MyFloat float, MyStr varchar(50))
INSERT INTO #temp VALUES (12345,'12345'),(123456789,'123456789')
SELECT len(MyFloat), len(MyStr) FROM #temp

Then look at the XML of the execution plan you’ll see these two important lines:

<ScalarOperator ScalarString="len(CONVERT_IMPLICIT(varchar(23),[tempdb].[dbo].[#temp].[MyFloat],0))">
<ScalarOperator ScalarString="len([tempdb].[dbo].[#temp].[MyStr])">

Which show you that the FLOAT had to be converted to VARCHAR. You can see the same thing if you try it with various versions of INT or DATE datatypes as well. Like I said earlier. No big deal with INT or even DATE. Those come back in a fairly expected format. (INTs look exactly the same and DATEs come back as ‘YYYY-MM-DD’). FLOAT and REAL however are floating point so they don’t always convert the same way. If you do the conversion deliberately you get this:

SELECT CONVERT(varchar(23), MyFloat) FROM #temp

Obviously this conversion completely messes us up if we wanted the number of characters we were seeing when we did the insert.

So how about using DATALENGTH? Well I’ve talked about the difference between LEN and DATALENGTH before so I’m not going to go into this in depth. But suffice it to say that DATALENGTH gives you the storage used by the value. This changes a great deal depending on your datatype so it’s not going to work either.

So the final answer? Datatype matters! Assuming that what you see is the same as what the computer sees isn’t going to work!

Filed under: Microsoft SQL Server, Query Plans, SQLServerPedia Syndication, T-SQL Tagged: data types, microsoft sql server, T-SQL