Using LEN wrong.

4

June 2, 2016 by Kenneth Fisher

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

ConvertFloat

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!

4 thoughts on “Using LEN wrong.

  1. I ran across this code in SQL once:

    CREATE PROCEDURE TestProcedure
    @parameter bit = false
    AS
    BEGIN

    END

    It worked but relied on all sorts of legacy code and assumptions to do so.

  2. Wayne says:

    You don’t have to go to the XML plan to see this, the conventional execution plan also shows it. The final (leftmost) Select icon shows the yellow triangle warning symbol and hovering over it shows the implicit conversion problem.

  3. […] Fisher had a recent blog post on a question that was posted to Stack Overflow.  The interesting bit was someone using the LEN() […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: